REGEXP_ILIKE
Returns true if the string contains a match for the regular expression. This function is similar to the LIKE-predicate, except that it uses a case insensitive regular expression, rather than simple wildcard character matching.
Syntax
REGEXP_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 several strings to demonstrate regular expressions.
- Create a table (
longvc
) with a single, long varchar column (body
) and insert data with some distinct characters: - Pattern match table rows containing a specific character (
'ç'
), added as part of Step 1: - Select all rows that 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_ilike(body, 'ç'); body ------------------------------------------------- Je peux manger du verre, ça ne me fait pas mal. (1 row)
=> SELECT * FROM longvc where regexp_ilike(body, 'a'); body ------------------------------------------------- Je peux manger du verre, ça ne me fait pas mal. Voin syödä lasia, se ei vahingoita minua zésbaésbaa (3 rows)