10/6/10

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.

3 comments:

  1. Hi Shyam

    I 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

    ReplyDelete
  2. number of options available with the use of this operator here: http://psoug.org/reference/regexp.html

    ReplyDelete
  3. How to use REGEXP_LIKE in PS Query

    ReplyDelete

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month