REGEXP_NOT_LIKE

Returns true if the string does not contain a match for the regular expression.

Returns true if the string does not contain a match for the regular expression. REGEXP_NOT_LIKE is a case sensitive regular expression.

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

Syntax

REGEXP_NOT_LIKE ( 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 the 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. Use REGEXP_NOT_LIKE to return rows that do not contain the character ç:

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

    => SELECT * FROM longvc where regexp_not_like(body, '.*ö.*ä');
                            body
    ----------------------------------------------------
     Je peux manger du verre, ça ne me fait pas mal.
     zésbaésbaa
     На берегу пустынных волн
     私はガラスを食べられます。それは私を傷つけません。
    (4 rows)
    
  4. Pattern match all rows that do not contain the characters z and *ésbaa:

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