REGEXP_NOT_ILIKE

Returns true if the string does not match the case-insensitive regular expression.

Syntax

REGEXP_NOT_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 strings to demonstrate regular expressions.

  1. Create a table (longvc) with a single, long varchar column (body). Then, insert data with some distinct characters, and query the table contents:
  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. Use REGEXP_NOT_ILIKE to return rows that do not contain a specific character ('ç'):
  4. => SELECT * FROM longvc where regexp_not_ilike(body, 'ç');
                            body
    ----------------------------------------------------
     Voin syödä lasia, se ei vahingoita minua
     zésbaésbaa
     На берегу пустынных волн
     私はガラスを食べられます。それは私を傷つけません。
    (4 rows)
    
    
  5. Pattern match all rows that do not contain the substring 'a'
  6. => SELECT * FROM longvc where regexp_not_ilike(body, 'a');
                            body
    ----------------------------------------------------
     На берегу пустынных волн
     私はガラスを食べられます。それは私を傷つけません。
    (2 rows)