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

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
      ,name
      ,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$');

FIRST_NAME           LAST_NAME
-------------------- -------------------------
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.

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

REGEXP_INSTR
------------
          37

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.

SELECT
  REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM employees;

REGEXP_REPLACE
--------------------------------------------------------------------------------
(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; 

     COUNT
----------
         3

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.

Related

References

• Documentation- Syntax and best examples to use Regexp in Oracle