REGEXP_NOT_LIKE

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

Syntax

REGEXP_NOT_LIKE( string, pattern  modifiers ] )

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.

modifiers

[Optional] One or more single-character flags that modify how the regular expression finds matches in string:

b

Treat strings as binary octets, rather than UTF-8 characters.

c

Force the match to be case sensitive (the default).

i

Force the match to be case insensitive.

m

Treat the string to match as multiple lines. Using this modifier, the start of line (^) and end of line ($) regular expression operators match line breaks (\n) within the string. Without the m modifier, the start and end of line operators match only the start and end of the string.

n

Allow the single character regular expression operator (.) to match a newline (\n). Without the n modifier, the . operator matches any character except a newline.

x

Add comments to your regular expressions. Using the x modifier causes the function to ignore all unescaped space characters and comments in the regular expression. Comments start with a hash (#) character and end with a newline (\n). All spaces in the regular expression that you want to be matched in strings must be escaped with a backslash (\) character.

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

These examples demonstrate the REGEXP_NOT_LIKE regular expression function.

  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_LIKE to return rows that do not contain a specific character ('ç'):
  4. => SELECT * FROM longvc where regexp_not_like(body, 'ç');
                            body
    ----------------------------------------------------
     Voin syödä lasia, se ei vahingoita minua
     zésbaésbaa
     На берегу пустынных волн
     私はガラスを食べられます。それは私を傷つけません。
    (4 rows)
    
  5. Return all rows that do not contain these characters ('.*ö.*ä'): 
  6. => 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)
    
  7. Pattern match all rows that do not contain these specific characters ('z.*ésbaa'): 
  8. => 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)