REGEXP_ILIKE

Returns true if the string contains a match for the regular expression. REGEXP_ILIKE is similar to the LIKE predicate, except that it uses a case insensitive regular expression, rather than simple wildcard character matching.

This function operates on UTF-8 strings using the default locale, even if the locale is set otherwise.

Syntax

REGEXP_ILIKE ( string-expression, pattern )

Parameters

string-expression

The VARCHAR or LONG VARCHAR expression to evaluate for matches with the regular expression specified in pattern. If string-expression is in the __raw__ column of a flex or columnar table, cast the string to a LONG VARCHAR before searching for pattern.

pattern

The regular expression to match against string-expression. The regular expression must conform with Perl regular expression syntax.

Examples

This example creates a table containing several strings to demonstrate regular expressions.

1. Create table longvc with a single, long varchar column body, and insert data with distinct characters:

   => CREATE table longvc(body long varchar (1048576));
   CREATE TABLE
   
   => insert into longvc values ('На берегу пустынных волн');
   => insert into longvc values ('Voin syödä lasia, se ei vahingoita minua');
   => insert into longvc values ('私はガラスを食べられます。それは私を傷つけません。');
   => insert into longvc values ('Je peux manger du verre, ça ne me fait pas mal.');
   => insert into longvc values ('zésbaésbaa');
   => insert into longvc values ('Out of the frying pan, he landed immediately in the fire');
   
   => SELECT * FROM longvc;
                         body
   ------------------------------------------------
    На берегу пустынных волн
    Voin syödä lasia, se ei vahingoita minua
    私はガラスを食べられます。それは私を傷つけません。
    Je peux manger du verre, ça ne me fait pas mal.
    zésbaésbaa
    Out of the frying pan, he landed immediately in the fire
   (6 rows)
   

2. Pattern match table rows containing the character ç:

   => SELECT * FROM longvc where regexp_ilike(body, 'ç');
                         body
   -------------------------------------------------
    Je peux manger du verre, ça ne me fait pas mal.
   (1 row)
   

3. Select all rows that contain the characters A/a:

   => SELECT * FROM longvc where regexp_ilike(body, 'A');
                         body
   -------------------------------------------------
    Je peux manger du verre, ça ne me fait pas mal.
    Voin syödä lasia, se ei vahingoita minua
    zésbaésbaa
   (3 rows)
   

4. Select all rows that contain the characters O/o:

   => SELECT * FROM longvc where regexp_ilike(body, 'O');
                              body
   ----------------------------------------------------------
    Voin syödä lasia, se ei vahingoita minua
    Out of the frying pan, he landed immediately in the fire
   (2 rows)