REGEXP_NOT_ILIKE

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

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

Syntax

REGEXP_NOT_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

1. Create a table (longvc) with a single, long varchar column (body). Then, insert data with some distinct characters, and query the table contents:

   => 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)
   

2. Find all rows that do not contain the character ç:

   
   => SELECT * FROM longvc where regexp_not_ilike(body, 'ç');
                           body
   ----------------------------------------------------
    Voin syödä lasia, se ei vahingoita minua
    zésbaésbaa
    На берегу пустынных волн
    私はガラスを食べられます。それは私を傷つけません。
   (4 rows)
   

3. Find all rows that do not contain the substring a:

   => SELECT * FROM longvc where regexp_not_ilike(body, 'a');
                           body
   ----------------------------------------------------
    На берегу пустынных волн
    私はガラスを食べられます。それは私を傷つけません。
   (2 rows)