INSTR Function - Oracle to MariaDB Migration

In Oracle the INSTR function allows you to find the position of substring in a string. It accepts 2, 3 or 4 parameters.

MariaDB also has the INSTR function, but it accepts 2 parameters only, so you have to use LOCALE or user-defined function when converting Oracle INSTR with more than 2 parameters.

INSTR with 2 parameters searches the specified substring from the beginning of string:

Oracle:

  -- Find substring in string 
  SELECT INSTR('abc', 'b') FROM dual;
  # 2

No conversion required if INSTR is with 2 parameters.

MariaDB:

  -- Find substring in string 
  SELECT INSTR('abc', 'b');
  # 2

INSTR with 3 parameters starts searching the specified substring from the specified position of string:

Oracle:

  -- Find substring in string starting from 3 position
  SELECT INSTR('abcb', 'b', 3) FROM dual;
  # 4

In MariaDB you have to use the LOCATE function now. Note that order of parameters is different in INSTR and LOCATE:

MariaDB:

  -- Find substring in string starting from 3 position
  SELECT LOCATE('b', 'abcb', 3);
  # 4

INSTR with 4 parameters starts searching the specified substring from the specified position and returns the position of the specified occurrence of string:

Oracle:

  -- Find 2nd occurrence of substring in string starting from 3 position
  SELECT INSTR('abcbcb', 'b', 3, 2) FROM dual;
  # 6

In MariaDB you have to use an user-defined function to find the Nth occurrence of substring:

MariaDB:

  DELIMITER //
 
  CREATE FUNCTION INSTR4 (p_str VARCHAR(8000), p_substr VARCHAR(255), 
     p_start INT, p_occurrence INT)
  RETURNS INT
  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 ;

So now you can use this user-defined function for INTR with 4 parameters in MariaDB:

MariaDB:

  -- Find 2nd occurrence of substring in string starting from 3 position
  SELECT INSTR4('abcbcb', 'b', 3, 2);
  # 6

Also some Oracle code can use INSTR function with 4 parameters, but 3rd and 4th parameters having values of 1. In this case the user-defined function is not required and INSTR function with 2 parameters can be used in MariaDB:

Oracle:

  -- 4 parameters are specified, but 3rd and 4th have default values
  SELECT INSTR('abcbcb', 'b', 1, 1) FROM dual;
  # 2

So this call can be converted to INSTR with 2 parameters in MariaDB:

MariaDB:

  SELECT INSTR('abcbcb', 'b');
  # 2

For more information, see Oracle to MariaDB Migration.