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