How to Use REGEXP_SUBSTR in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
REGEXP_SUBSTR(source, pattern, position, occurrence, match_param, subexpr) extracts matches; subexpr returns one capture group instead of the full match.
SELECT REGEXP_SUBSTR('order-2024-0091-a', '[0-9]+') AS first_number
FROM dual;Occurrence and capture groups
The fourth argument, occurrence, selects the nth match rather than the first. The sixth argument, subexpr, returns just one parenthesized capture group from that match instead of the whole matched substring, which avoids a separate REGEXP_REPLACE step.
SELECT REGEXP_SUBSTR('2024-08-25', '(\d{4})-(\d{2})-(\d{2})', 1, 1, NULL, 2) AS month_part
FROM dual;Asking for a subexpr group number higher than the pattern actually defines returns NULL rather than raising an error, so a typo in the pattern (one pair of parentheses too few) silently produces nulls instead of failing loudly. Check the pattern's group count first if results come back empty.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free