REGEXP_NOT_ILIKE
Returns true if the string does not match the case-insensitive regular expression.
Syntax
REGEXP_NOT_ILIKE( string, pattern )
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. |
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
This example creates a table containing strings to demonstrate regular expressions.
- 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_ILIKE
to return rows that do not contain a specific character ('ç'
): - Pattern match all rows that do not contain the substring
'a'
:
=> 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_ilike(body, 'ç'); body ---------------------------------------------------- Voin syödä lasia, se ei vahingoita minua zésbaésbaa На берегу пустынных волн 私はガラスを食べられます。それは私を傷つけません。 (4 rows)
=> SELECT * FROM longvc where regexp_not_ilike(body, 'a'); body ---------------------------------------------------- На берегу пустынных волн 私はガラスを食べられます。それは私を傷つけません。 (2 rows)