Header Ads

  • Recent Posts

    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.

    Post Top Ad

    Post Bottom Ad