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.

Important

If you port 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.

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

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)

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)

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)

Pattern match all rows that do not contain the characters z and *ésbaa:

=> SELECT * FROM longvc where regexp_not_like(body, 'z.*é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)