Thursday, May 29, 2008

Example of REGEXP_LIKE and explanation of back references

Requirement:

Suppose we have column named Name1 (consisting of first name and last name) with following data:

Name1
-------------
Arpu Anau
Stev Smith

I want to match first and last characters of first and last names. If they are same show that row or show TRUE for that row.
Means
Arpit Anau is having A=A and u=u. So, TRUE
Stev Smith is having S=S but v!=h. So, FALSE

Example without Regular Exprssion:

WITH t AS
(
SELECT 'Arpu Anau' Name1 FROM DUAL UNION ALL
SELECT 'Stev Smith' Name1 FROM DUAL
)
SELECT FN, LN, CASE WHEN SUBSTR(FN,1,1) = SUBSTR(LN,1,1) AND SUBSTR(FN,-1,1) = SUBSTR(LN,-1,1) THEN 'TRUE' ELSE 'FALSE' END
FROM (SELECT SUBSTR(Name1,1,INSTR(Name1,' ')-1) FN, SUBSTR(Name1,INSTR(Name1,' ')+1) LN
FROM t)
SQL> /

FN LN CASEW
---------- ---------- -----
Arpu Anau TRUE
Stev Smith FALSE


With Regular Expresion [example uses backreferences in Regular Expression]

SQL> WITH t AS
(
SELECT 'Antu Anamentu' Name1 FROM DUAL UNION ALL
SELECT 'Steven Smith' Name1 FROM DUAL
)
SELECT Name1
FROM t
WHERE REGEXP_LIKE (Name1, '^(.).*(.) \1.*\2$') ;

NAME1
-------------
Antu Anamentu

Explanation:
^(.) =>^ specifies the next char is first char
. dot specifies single character
() defines group which can be backrferenced
.* => Any number of characters as we want only first and last character
(.) => (.)space The space signifies that we want to remember last char of first word or char before space
\1 => It recalls first group which we saved for backreference using (.)
.* => Any number of chars
\2 => It recalls first group which we saved for backreference using (.)
$ => It specifies the char before this is last char

1 comment:

Anonymous said...

Do you happen to know how to match any string with more than one certain characters in any order? For example, find all string with 'a', 'g','z' in any order.