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

How to split numeric and text values into separate columns

Puzzle: There is a table T with a single column C VARCHAR2(20) that contains random values. Some of the values are numeric. Write a single SELECT statement that outputs 2 columns: NUM and TEXT with numeric and non-numeric values correspondingly.

To mimic the T table, we will create a view:

CREATE OR REPLACE VIEW T
AS
WITH x AS (
SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
            ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
       END rnd,
       ROW_NUMBER()OVER(ORDER BY dbms_random.value) rk
FROM dual
CONNECT BY ROWNUM<=40
)
SELECT CAST(rnd AS VARCHAR2(4)) C
FROM x
WHERE rk<=10;

Expected Result:
Due to the random nature of the values in T view, actual results will be different every time you run a query; however, all the results will look somewhat like this:

NUM  TEXT
---- ----
-146 4R9
-362 78R
-762 ICY
236  U3W
     VIK
     Y21

Solution:

WITH x AS (
SELECT c, CASE WHEN REGEXP_LIKE(c,'^-?[[:digit:]]+$') THEN 1 ELSE 0 END is_int,
       RANK()OVER(PARTITION BY CASE WHEN REGEXP_LIKE(c,'^-?[[:digit:]]+$') THEN 1 ELSE 0 END ORDER BY c) rk
FROM t	   
)
SELECT MAX(DECODE(is_int, 1, c)) NUM,
       MAX(DECODE(is_int, 0, c)) TEXT
FROM x
GROUP BY rk
ORDER BY rk

Explanation:

The above solution uses regular expression for identifying numeric integer values (positive and negative):

^-?[[:digit:]]+$

The way to break down the results into two columns was demonstrated in my previous post: Generate a department/employee roll report

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

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

 

How to generate a random sample of numeric and alpha-numeric values

Puzzle: Generate a random sample of numeric and alpha-numeric values

Level: Intermediate/Advanced

This problem often arises when you need to run a test and you don’t have a table to experiment with. Not a problem any more!

Related Post: Use TRUNC function to generate various date ranges

Step 1: Generate a random list of 10 alpha-numeric values:

SELECT dbms_random.string('x',3) rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
-----
3TI
1JB
CIP
9SE
79K
YNZ
VEG
V0B
KPN
ILR

Step 2: Generate a random list of 10 integer values:

SELECT TRUNC(dbms_random.VALUE(-999,999)) rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
----
 539
 153
 979
 689
 212
 267
  -5
-832
-160
 665

Step 3: Mix the above lists together (with equal shares):

SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
            ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
       END rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
-------
-513
SVA
-475
NRM
-903
G45
-654
2S3
415
0HG

Step 4: Let’s have a fairly random number of integers and strings in the output

WITH x AS (
SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
            ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
       END rnd,
       ROW_NUMBER()OVER(ORDER BY dbms_random.value) rk
FROM dual
CONNECT BY ROWNUM<=40
)
SELECT CAST(rnd AS VARCHAR2(4)) rnd
FROM x
WHERE rk<=10

RND
----
-985
0TG
8JZ
-714
500
199
7IJ
249
RNI
F2G

Step 5: Final touch – let’s add a column that would flag integers

WITH x AS (
SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
            ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
       END rnd,
       ROW_NUMBER()OVER(ORDER BY dbms_random.value) rk
FROM dual
CONNECT BY ROWNUM<=40
)
SELECT CAST(rnd AS VARCHAR2(4)) rnd, CASE WHEN REGEXP_LIKE(rnd,'^-?[[:digit:]]+$') THEN 1 ELSE 0 END is_int
FROM x
WHERE rk<=10

RND      IS_INT
---- ----------
WIS           0
-558          1
0QR           0
-433          1
RB0           0
PT8           0
409           1
YOV           0
969           1
FFI           0

 

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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

A function that converts a specially formatted character string to Oracle’s TIMESTAMP data type.

Problem: Convert a character string formatted ‘YYYY-MM-DD HH24:MI:SS.FF’ to TIMESTAMP. The string has to be validated before converting VARCHAR2 variable to TIMESTAMP.

The following function does the trick:

CREATE OR REPLACE FUNCTION TO_TS(p_ts_text VARCHAR2) RETURN TIMESTAMP
AS
BEGIN
    IF REGEXP_LIKE(p_ts_text, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{2}') THEN
        RETURN TO_TIMESTAMP(p_ts_text, 'YYYY-MM-DD HH24:MI:SS.FF');
    END IF;
    
    RAISE_APPLICATION_ERROR(-20012,'Invalid timestamp format is used');
END;

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.