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. 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.

Syntax

REGEXP_LIKE( string, pattern[, regexp‑modifier ]… )

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.
regexp‑modifier

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.

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)