Oracle Operator - REGEXP_LIKE
One of my blog readers told me about REGEXP_LIKE condition in Oracle and how it could be used to perform pattern matching. Thanks Ram, I learnt a new operator that day.
The REGEXP_LIKE condition is like the LIKE condition providing additional capabilities. It performs regular POSIX and Unicode expression matching too unlike the LIKE condition.
The syntax is:
WHERE
REGEXP_LIKE (source_string,pattern_to_match, match_parameter)
AND 'any_other_usual_conditions'
An example:
SELECT EMPLID, NAME
FROM PS_NAMES
WHERE REGEXP_LIKE (NAME, '^J.(N|I),S$');
The query will return all employees with NAMES that start with J, followed by any letter, then either N or I, then any letter, then the letter S .
EMPLID NAME
-------- -----------------
000001 JONES
001202 JAMES
Another example along with explanation in detail from Oracle download site.
The REGEXP_LIKE condition is like the LIKE condition providing additional capabilities. It performs regular POSIX and Unicode expression matching too unlike the LIKE condition.
The syntax is:
WHERE
REGEXP_LIKE (source_string,pattern_to_match, match_parameter)
AND 'any_other_usual_conditions'
An example:
SELECT EMPLID, NAME
FROM PS_NAMES
WHERE REGEXP_LIKE (NAME, '^J.(N|I),S$');
The query will return all employees with NAMES that start with J, followed by any letter, then either N or I, then any letter, then the letter S .
EMPLID NAME
-------- -----------------
000001 JONES
001202 JAMES
Another example along with explanation in detail from Oracle download site.
Hi Shyam
ReplyDeleteI had a scenario, where I should insert numbers onto a varchar field, but it should only have numbers and not alphabets.
Creating a constraint with REGEXP as follows helped me out.
CREATE TABLE test(column1 varchar(200) CHECK (REGEXP_LIKE (column1, '^[[:digit:]]+$')));
Thanks a lot for your post on REGEXP which helped me to get this done :)
Cheers
Yamuna
number of options available with the use of this operator here: http://psoug.org/reference/regexp.html
ReplyDeleteHow to use REGEXP_LIKE in PS Query
ReplyDelete