Explain REGEXP_LIKE, REGEXP_SUBSTR and REGEXP_REPLACE in PL/SQL

How it works

REGEXP_LIKE(string, pattern) is a boolean condition for pattern matching, usable directly in a WHERE clause — unlike LIKE, it supports full regular expression syntax, not just % and _. REGEXP_SUBSTR(string, pattern, [position], [occurrence]) extracts the substring that matches. REGEXP_REPLACE(string, pattern, replacement) substitutes matches, and supports backreferences (\1, \2, ...) to reuse captured groups in the replacement.

SELECT
  REGEXP_SUBSTR('Invoice INV-2026-00842', '[0-9]+', 1, 2)                     AS invoice_number,
  REGEXP_REPLACE('555.123.4567', '(\d{3})\D(\d{3})\D(\d{4})', '(\1) \2-\3') AS formatted_phone
FROM dual
WHERE REGEXP_LIKE('INV-2026-00842', '^INV-\d{4}-\d+$');
-- invoice_number   => '00842'
-- formatted_phone  => '(555) 123-4567'

Related Questions