REGEXP_LIKE

Returns true if the string matches the regular expression. REGEXP_LIKE 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 is set otherwise.

If you port 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-expression, pattern [, regexp‑modifier ]… )

Parameters

string-expression The VARCHAR or LONG VARCHAR expression to evaluate for matches with the regular expression specified in pattern. If string-expression is in the __raw__ column of a flex or columnar table, cast the string to a LONG VARCHAR before searching for pattern.
pattern

The regular expression to match against string-expression. The regular expression must conform with Perl regular expression syntax.
regexp‑modifier

One or more single-character flags that modify how the regular expression pattern is matched to string-expression:

  • b: Treat strings as binary octets, rather than UTF-8 characters.
  • c (default): Force the match to be case sensitive.
  • 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: Match the regular expression operator (.) to a newline (\n). By default, the . operator matches any character except a newline.
  • x: Add comments to regular expressions. The x modifier causes the function to ignore all un-escaped space characters and comments in the regular expression. Comments start with hash (#) and end with a newline (\n). All spaces in the regular expression to be matched in strings must be escaped with a backslash (\).

Examples

Create a table that contains several strings:

=> 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 from table t that contain the letter a:

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

Select all rows from table t 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 table t:

=> 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 from table t 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)