Oracle INSTR replacement in MySQL

Question

Requirements: Before, I used instr() in Oracle to achieve the requirements, but now I want to use MySQL to achieve the same effect, and try to use the functions in MySQL to achieve it.

INSTR(A.SOME_THING.B,".",1,2)<>0 --ORACLE

Answer 1

As far as I can tell, that's not that difficult for simple cases. But, as number of parameters raises, MySQL "replacement" for the same Oracle functionality gets worse.

As your code:

instr(some_thing, '.', 1, 2)

means

• search through some_thing
• for a dot
• starting from the first position
• and find dot's second occurrence

you can't do that in a simple manner using MySQL, as you'll need a user-defined function. Something like this (source is INSTR Function - Oracle to MySQL Migration; I suggest you have a look at the whole document. I'm posting code here because links might get broken):

 DELIMITER //
 
  CREATE FUNCTION INSTR4 (p_str VARCHAR(8000), p_substr VARCHAR(255), 
     p_start INT, p_occurrence INT)
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE v_found INT DEFAULT p_occurrence;
    DECLARE v_pos INT DEFAULT p_start;
 
    lbl:
    WHILE 1=1 
     DO
    -- Find the next occurrence
    SET v_pos = LOCATE(p_substr, p_str, v_pos);
 
    -- Nothing found
    IF v_pos IS NULL OR v_pos = 0 THEN
      RETURN v_pos;
    END IF;
 
    -- The required occurrence found
    IF v_found = 1 THEN
      LEAVE lbl;
    END IF;
 
    -- Prepare to find another one occurrence
    SET v_found = v_found - 1;
    SET v_pos = v_pos + 1;
    END WHILE;
 
    RETURN v_pos;
  END;
  //
 
  DELIMITER ;

Use it as

SELECT INSTR4('abcbcb', 'b', 3, 2);

and get 6 as a result.

Answer 2

In OracleDB the code

INSTR(column, ".", 1, 2) <> 0 --ORACLE

checks does the column contains at least 2 point chars in the value.

In MySQL this can be replaced with, for example,

LENGTH(column) - LENGTH(REPLACE(column, '.', '')) >= 2