Mimic LIKE ANY in Oracle SQL

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s