REGEXP_LIKE

Returns true if the string matches the regular expression. This function is similar to the LIKE-predicate, except that it uses regular expressions rather than simple wildcard character matching.

Syntax

REGEXP_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

This example creates a table containing several strings to demonstrate regular expressions.

=> CREATE TABLE t (v VARCHAR);
CREATE TABLE
=> CREATE PROJECTION t1 AS SELECT * FROM t;
CREATE PROJECTION
=> COPY t FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> aaa
>> Aaa
>> abc
>> abc1
>> 123
>> \.
=> SELECT * FROM t;
   v   
-------
 aaa
 Aaa
 abc
 abc1
 123
(5 rows)

Select all records in the table that contain the letter "a."

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'a');
  v   
------
 Aaa
 aaa
 abc
 abc1
(4 rows)

Select all of the rows in the table that start with the letter "a."

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'^a');
  v   
------
 aaa
 abc
 abc1
(3 rows)

Select all rows that contain the substring "aa."

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'aa');
  v  
-----
 Aaa
 aaa
(2 rows)

Select all rows that contain a digit.

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'\d');
  v   
------
 123
 abc1
(2 rows)

Select all rows that contain the substring "aaa."

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'aaa');
  v  
-----
 aaa
(1 row)

Select all rows that contain the substring "aaa" using case insensitive matching.

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'aaa', 'i');
  v  
-----
 Aaa
 aaa
(2 rows)

Select rows that contain the substring "a b c."

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'a b c');
 v 
---
(0 rows)

Select rows that contain the substring "a b c" ignoring space within the regular expression.

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'a b c','x');
  v   
------
 abc
 abc1
(2 rows)

Add multi-line rows to demonstrate using the "m" modifier.

=> COPY t FROM stdin RECORD TERMINATOR '!';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> Record 1 line 1
>> Record 1 line 2
>> Record 1 line 3!
>> Record 2 line 1
>> Record 2 line 2
>> Record 2 line 3!
>> \.

Select rows that start with the substring "Record" and end with the substring "line 2."

=> SELECT v from t WHERE REGEXP_LIKE(v,'^Record.*line 2$');
 v 
---
(0 rows)

Select rows that start with the substring "Record" and end with the substring "line 2," treating multiple lines as separate strings.

=> SELECT v from t WHERE REGEXP_LIKE(v,'^Record.*line 2$','m');
                        v                         
--------------------------------------------------
 
Record 2 line 1
Record 2 line 2
Record 2 line 3
Record 1 line 1
Record 1 line 2
Record 1 line 3
(2 rows)