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

LIKE with ANY in Teradata SQL

Teradata happened to support a very convenient SQL feature that Oracle does not have:

Teradata:

SELECT *
FROM emp
WHERE ename LIKE ANY ('%A%', '%B%');

--Works perfectly fine

Oracle:

SELECT *
FROM emp
WHERE ename LIKE ANY ('%A%', '%B%');

WHERE ename LIKE ANY ('%A%', '%B%')
                 *
ERROR at line 3:
ORA-00936: missing expression

Oracle does not seem to support any combination of LIKE and ANY:

SELECT *
FROM emp
WHERE ename LIKE ANY (SELECT '%A%' FROM dual UNION ALL
                      SELECT '%B%' FROM dual);

WHERE ename LIKE ANY (SELECT '%A%' FROM dual UNION ALL
                 *
ERROR at line 3:
ORA-00936: missing expression

An interview question that checks knowledge of Oracle SQL character functions.

Level: Beginner

Question: Find all employee names beginning with ‘S’ not using LIKE operator.

Method #1: INSTR function

SELECT ename
FROM emp
WHERE INSTR(ename, 'S')=1
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #2: Range Test

SELECT ename
FROM emp
WHERE ename>='S' AND ename<'T'
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #3: SUBSTR function

SELECT ename
FROM emp
WHERE SUBSTR(ename, 1, 1) = 'S'
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #4: ASCII function

SELECT ename
FROM emp
WHERE ASCII(ename)=ASCII('S')
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #5: SUBSTR functions

SELECT ename
FROM emp
WHERE 'S' || SUBSTR(ename, 2) = ename
/

--Result:

ENAME
-------
SMITH
SCOTT

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.