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.

How to enforce email address column to accept only “valid” email values?

The easiest way to solve this problem is to add a check constraint with regular expression validation:

--Let's first add an email column to the emp table:

ALTER TABLE emp ADD email VARCHAR2(100);

Table altered.

--Now let's add constraint using REGEXP_LIKE function:

ALTER TABLE emp ADD CONSTRAINT chk_emp_email 
CHECK (REGEXP_LIKE (EMAIL,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'));

Table altered.

Now let’s see if it works:

UPDATE emp
SET email=ename || '@emp.com'
WHERE ROWNUM=1;

1 row updated.

UPDATE emp
SET email = 'abc@' --invalid email
WHERE ROWNUM=1;
UPDATE emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_EMP_EMAIL) violated

You may want to drop the new column after this exercise:

ALTER TABLE emp DROP COLUMN email;
Table altered.

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