How to use Regular Expressions in SQL

Recently I had a need to check if a column contains non-numeric/alpha-numeric values for identifying anomalies of a design.
The first thing that struck me was, RegEx a.k.a Regular Expressions, which would make it simpler to search. I use them often in tools like notepad++ and in programming languages like Java. Then I wanted to know if Oracle SQL supports Regular Expressions. Fortunately, yes and here is the summary of my exploration.

Here are the four important Regular Expression, SQL functions supported by Oracle:
REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SUBSTR

Example:

[sql]
create table employee (name varchar2(255), id varchar2(255));
INSERT ALL
INTO employee (name,id) VALUES ('Arya', '123')
INTO employee (name,id) VALUES ('Chari', '456')
INTO employee (name,id) VALUES ('Sam', '789')
INTO employee (name,id) VALUES ('Bond', 'abc-123')
INTO employee (name,id) VALUES ('Sudhakar', '111-xyz')
SELECT 1 FROM DUAL;
commit;
SQL> select * from employee;

NAME                           ID
------------------------------ ------------------------------
Arya                           123
Chari                          456
Sam                            789
Bond                           abc-123
Sudhakar                       111-xyz

-- The regular expression I used here is a very basic one, which looks for Alphabets from a-z in both lower and upper case.
SQL> select * from employee where REGEXP_LIKE(ID, '[a-zA-Z]');

NAME                           ID
------------------------------ ------------------------------
Bond                           abc-123
Sudhakar                       111-xyz

SQL> select ID, REGEXP_REPLACE(ID, '[a-zA-Z]','X') REPLACED_STR from employee;

ID                             REPLACED_STR
------------------------------ -------------------------------------------------
123                            123
456                            456
789                            789
abc-123                        XXX-123
111-xyz                        111-XXX

SQL> select ID, REGEXP_INSTR(ID, '[a-zA-Z]') INSTR from employee;

ID                                  INSTR
------------------------------ ----------
123                                     0
456                                     0
789                                     0
abc-123                                 1
111-xyz                                 5

SQL> select ID, REGEXP_SUBSTR(ID, '[a-zA-Z]+') SUBSTR from employee;

ID                             SUBSTR
------------------------------ ------------------------------------------------
123
456
789
abc-123                        abc
111-xyz                        xyz
[/sql]

As RegEx is very powerful, when coupled with SQL, can be used to simplify queries.

Leave a Comment