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.