skip to Main Content

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

2

Answers


  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.

    Login or Signup to reply.
  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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search