REGEXP_SUBSTR Function - Oracle to MariaDB Migration

In Oracle and MariaDB, the REGEXP_SUBSTR function returns the substring that matches the regular expression.

Oracle's REGEXP_SUBSTR allows specifying four parameters, including the starting position and the Nth occurrence, while MariaDB's REGEXP_SUBSTR allows specifying only two parameters.

Oracle:

  -- Match a string until a comma and return the 2nd occurrence
  SELECT REGEXP_SUBSTR('aa,bbb,cccc', '[^,]+', 1, 2) FROM dual;
  /* bbb */
 
  -- Match a string until a blank and return the 3rd occurrence
  SELECT REGEXP_SUBSTR('aa bbb cccc', '[^[:blank:]]+', 1, 3) FROM dual;
  /* cccc */
 
  -- Start at position 4 and return the 1st occurrence
  SELECT REGEXP_SUBSTR('aa bbb cccc', '[^[:blank:]]+', 4, 1) FROM dual;
  /* bbb */

MariaDB:

  -- Match a string until a comma and return the 2nd occurrence
  SELECT REGEXP_SUBSTR('aa,bbb,cccc', '[^,]+', 1, 2);
  /* ERROR 1582 (42000): Incorrect parameter count in the call to native function 'REGEXP_SUBSTR' */
 
  -- Match a string until a blank and return the 3rd occurrence
  SELECT REGEXP_SUBSTR('aa bbb cccc', '[^[:blank:]]+', 1, 3);
  /* ERROR 1582 (42000): Incorrect parameter count in the call to native function 'REGEXP_SUBSTR'*/
 
  -- Start at position 4 and return the 1st occurrence
  SELECT REGEXP_SUBSTR(SUBSTR('aa bbb cccc', 4), '[^[:blank:]]+');
  /* bbb */

REGEXP_SUBSTR with 3 parameters starts searching for the specified pattern from the specified position in the string:

Oracle:

  -- Start at position 4
  SELECT REGEXP_SUBSTR('aa,bbb,cccc', '[^,]+', 4) FROM dual;
  /* bbb */

In MariaDB, the SUBSTR function can be used to extract a portion of the string before applying REGEXP_SUBSTR:

MariaDB:

  -- Start at position 4
  SELECT REGEXP_SUBSTR(SUBSTR('aa,bbb,cccc', 4), '[^,]+');
  /* bbb */

REGEXP_SUBSTR with 4 parameters starts searching for the specified pattern from the specified position and returns Nth occurrence:

Oracle:

  -- Start at position 4 and return the 2nd occurrence
  SELECT REGEXP_SUBSTR('aa,bbb,cccc', '[^,]+', 4, 2) FROM dual;
  /* cccc */

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

MariaDB:

  DELIMITER //
 
  CREATE FUNCTION SQLINES_REGEXP_SUBSTR(p_str VARCHAR(8000), p_pattern VARCHAR(255), 
     p_start INT, p_occurrence INT)
  RETURNS VARCHAR(8000)
  BEGIN
    DECLARE v_found INT DEFAULT p_occurrence;
    DECLARE v_pos INT DEFAULT p_start;
    DECLARE v_str VARCHAR(8000) DEFAULT p_str;
    DECLARE v_matched VARCHAR(8000);
 
    WHILE 1=1 DO
       -- Search substring
       SET v_str = SUBSTR(v_str, v_pos);
 
       -- Find the next occurrence
       SET v_matched = REGEXP_SUBSTR(v_str, p_pattern);
 
       -- Nothing found
       IF v_matched IS NULL THEN 
         RETURN NULL;
       END IF;
 
       -- The required occurrence found
       IF v_found = 1 THEN
         RETURN v_matched;
       END IF;
 
       -- Prepare to find the next occurrence
       SET v_found = v_found - 1;
       SET v_pos = LOCATE(v_matched, v_str) + LENGTH(v_matched);
    END WHILE;
 
    RETURN NULL;
  END;
  //
 
  DELIMITER ;

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

MariaDB:

  -- Start at position 4 and return the 2nd occurrence
  SELECT SQLINES_REGEXP_SUBSTR('aa,bbb,cccc', '[^,]+', 4, 2);
  /* cccc */

For more information, see Oracle to MariaDB Migration.