REGEXP_SUBSTR Function - Oracle to PostgreSQL Migration

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

In PostgreSQL, you can use an expression with a subquery, the REGEXP_MATCHES table-valued function, and the SUBSTR function.

Oracle:

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

PostgreSQL:

    -- Match a string until a comma and return the 2nd occurrence
  SELECT CURRENT_DATE, 'Found: ' || (SELECT m[1] FROM REGEXP_MATCHES('aa,bbb,cccc', '[^,]+', 'g') AS t(m) 
     OFFSET 2 - 1 LIMIT 1);
  /* 2026-01-13   Found: bbb */
 
  -- Match a string until a blank and return the 3rd occurrence
  SELECT CURRENT_DATE, 'Found: ' || (SELECT m[1] FROM REGEXP_MATCHES('aa bbb cccc', '[^[:blank:]]+', 'g') AS t(m)
    OFFSET 3 - 1 LIMIT 1);
  /*  2026-01-13   Found: cccc */
 
  -- Start at position 4 and return the 1st occurrence
  SELECT CURRENT_DATE, 'Found: ' || 
    (SELECT m[1] FROM REGEXP_MATCHES(SUBSTR('aa bbb cccc', 4), '[^[:blank:]]+', 'g') AS t(m) LIMIT 1);
  /*  2026-01-13   Found: bbb */

We used a subquery to demonstrate how the result of the REGEXP_MATCHES table-valued function can be used alongside other expressions and columns.

For more information, see Oracle to PostgreSQL Migration.