INSTR Function - Oracle to MariaDB Migration

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

Oracle's INSTR function also supports a negative starting position, meaning that it is counted backward from the end of the string and the search is performed backward from the resulting position.

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 for the specified substring from the beginning of the 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 for the specified substring from the specified position of string. The start position can be negative, which is often used to find the last occurrence:

Oracle:

  -- Find substring in string starting from 3 position
  SELECT INSTR('abcb', 'b', 3) FROM dual;
  /* 4 */
 
  -- Use negative start position to find the last occurrence of '.'
  SELECT INSTR('Document.Draft.pdf', '.', -1) FROM dual;
  /* 15 */

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

MariaDB:

  -- INSTR with 3 parameters is not supported
  SELECT INSTR('abcb', 'b', 3);
  /* ERROR 1582 (42000): Incorrect parameter count in the call to native function 'INSTR' */
 
  -- Find substring in string starting from 3 position
  SELECT LOCATE('b', 'abcb', 3);
  /* 4 */
 
  -- Negative start position is not supported!
  SELECT LOCATE('.', 'Document.Draft.pdf', -1);
  /* 0 */
 
  -- Expression to implement backward search (returns 0 if not found)
  SELECT (CHAR_LENGTH('Document.Draft.pdf') - LOCATE('.', REVERSE('Document.Draft.pdf')) + 1)
    * (LOCATE('.', 'Document.Draft.pdf') > 0);
  /* 15 */

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 */
 
  -- Search for 2nd occurrence backward!
  SELECT INSTR('abcbcb', 'b', -3, 2) FROM dual;
  /* 2 */

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

MariaDB:

  DELIMITER //
 
  CREATE FUNCTION SQLINES_INSTR (p_str VARCHAR(8000), p_substr VARCHAR(255), 
     p_start INT, p_occurrence INT)
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE v_str VARCHAR(8000) DEFAULT p_str;
    DECLARE v_substr VARCHAR(8000) DEFAULT p_substr;
    DECLARE v_found INT DEFAULT p_occurrence;
    DECLARE v_pos INT DEFAULT p_start;
 
    -- Search backward
    IF p_start < 0 THEN
      SET v_str = REVERSE(p_str);
      SET v_substr = REVERSE(p_substr);
      SET v_pos = v_pos * -1;
    END IF;
 
    lbl:
    WHILE 1=1 DO
      -- Find the next occurrence
      SET v_pos = LOCATE(v_substr, v_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;
 
    -- Calculate the resulting position if a backward search was performed
    IF p_start < 0 AND v_pos > 0 THEN
       SET v_pos = CHAR_LENGTH(p_str) - v_pos + 1; 
    END IF;
 
    RETURN v_pos;
  END;
  //
 
  DELIMITER ;

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

MariaDB:

  -- Find 2nd occurrence of substring in string starting from 3 position
  SELECT SQLINES_INSTR('abcbcb', 'b', 3, 2);
  /* 6 */
 
  -- Search for 2nd occurrence backward!
  SELECT SQLINES_INSTR('abcbcb', 'b', -3, 2);
  /* 2 */

If Oracle's INSTR uses 4 parameters but the start position is 1 and the occurrence is also 1, you can use MariaDB's INSTR with 2 parameters since these values are the defaults in MariaDB:

Oracle:

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

We can use INSTR with 2 parameters in MariaDB:

MariaDB:

  SELECT INSTR('abcbcb', 'b');
  /* 2 */

For more information, see Oracle to MariaDB Migration.