5 Top Oracle Regexp Functions and their Usage

Here are five regular expression functions supported in Oracle. Below, you will find examples and usage in SQL queries. These work in PL/SQL as well. In general,  the first argument is the source string and the second argument is the regular expression pattern.

Oracle Regular-expressions

  • regexp_like
  • regexp_substr
  • regexp_instr
  • regexp_replace
  • regexp_count

Regular-expression condition symbols

Regular expression condition symbols
Regular expression condition symbols

Oracle REGEXP examples

These five examples will help you understand the usage of regular expressions in Oracle.

1. regexp_substr

A table I have created. That contain telephone numbers. I used regexp_substr to extract telephone numbers.

create table tel_nums
(id          number
,name        varchar2(50)
,description clob

SQL query

The result has telephone numbers.

elect id
      ,regexp_substr (description, '...-...-....') phone
  from tel_nums;

2. regexp_like

SQL query

Below, you will find a query that shows how like works.

SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

-------------------- -------------------------
Steven               King
Steven               Markle
Stephen              Stiles

3. regexp_instr

Here is SQL query example examines the string, looking for occurrences of one or more non-blank characters. Oracle begins searching at the first character in the string and returns the starting position (default) of the sixth occurrence of one or more non-blank characters.

  REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
               '[^ ]+', 1, 6) "REGEXP_INSTR"


4. regexp_replace

SQL query

It checks the phone_number column for a pattern of the second argument. Then, the result would have in the format of the third argument.

                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM employees;

(515) 123-4567
(515) 123-4568
(515) 123-4569
(590) 423-4567
. . .

5. regexp_count

The function begins to evaluate the source string at the third character, so skips over the first occurrence of pattern:

SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL; 


These examples guide you to get a clear idea of how to use regular expressions in Oracle SQL queries. The same way you can use in PLSQL.



Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.