REGEXP_ILIKE

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

Syntax

REGEXP_ILIKE( string, pattern )

Parameters

string

The VARCHAR or LONG VARCHAR string to search for a regular expression pattern match. If string exists in a __raw__ column of a flex or columnar table, cast string to a LONG VARCHAR before searching for pattern.

pattern

A string containing the regular expression to match against the string. The syntax of the regular expression is compatible with the Perl 5 regular expression syntax. See the Perl Regular Expressions Documentation for details.

Notes

This function operates on UTF-8 strings using the default locale, even if the locale has been set to something else.

If you are porting a regular expression query from an Oracle database, remember that Oracle considers a zero-length string to be equivalent to NULL, while Vertica does not.

Examples

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

  1. Create a table (longvc) with a single, long varchar column (body) and insert data with some distinct characters:
  2. => 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');
    => 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
    (5 rows)
    
  3. Pattern match table rows containing a specific character ('ç'), added as part of Step 1:
  4. => SELECT * FROM longvc where regexp_ilike(body, 'ç');
                          body
    -------------------------------------------------
     Je peux manger du verre, ça ne me fait pas mal.
    (1 row)
    
  5. Select all rows that contain the substring 'a'
  6. => 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)