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
Subscribe to:
Post Comments (Atom)
1 comment:
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.
Post a Comment