Last year I wrote a small post on a unique feature of Teradata SQL: LIKE ANY operator. You can read it here. Recently I realized that we can mimic this functionality in Oracle using Regular Expressions.
For instance, if we need to find all employee whose names contain ‘AR’ or ‘AM’, we can do it in a traditional Oracle way:
SELECT ename FROM emp WHERE ename LIKE '%AR%' OR ename LIKE '%AM%'
Result:
ENAME --------- WARD MARTIN CLARK ADAMS JAMES
In Teradata, we would write it as following:
SELECT ename
FROM emp
WHERE ename LIKE ANY ('%AR%', '%AM%')
In Oracle we can use REGEXP_LIKE function:
SELECT ename FROM emp WHERE REGEXP_LIKE(ename, 'AR|AM')
Note, that in regular expression pattern we don’t use the wild card character ‘%’.
If we needed to see all employees whose name start with A or B, we would use a slightly different matching pattern:
SELECT ename FROM emp WHERE REGEXP_LIKE(ename, '^A|^B')
Result:
ENAME -------- ALLEN BLAKE ADAMS
For names ending on ‘N’ or ‘S’:
SELECT ename FROM emp WHERE REGEXP_LIKE(ename, 'N$|S$')
Result:
ENAME -------- ALLEN JONES MARTIN ADAMS JAMES
My Oracle Group on Facebook:
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles?
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.
sqlpatterns 