Vertica Regular Expression Functions and Examples

The Vertica regular expression functions are used to identify a precise pattern from the given string. The string could be variable or database table column name. The regular expressions are used in various applications such as extracting some numbers from string values, extracting alphanumeric values from string, extracting particular pattern from string, validating received data. You can also use regular expression to validate date, range check, extract only digits from given string etc.

You can use regular expression functions along with any built-in functions such as date type conversion functions, string functions etc.

In this article, we will check regular expression functions available in Vertica with some examples.

Vertica Regular Expression Functions

Below is the list of regular expression functions supported by Vertica:

REGEXP_SUBSTR
REGEXP_COUNT
REGEXP_LIKE
REGEXP_NOT_LIKE
REGEXP_ILIKE
REGEXP_NOT_ILIKE
REGEXP_INSTR
REGEXP_REPLACE

Now let us check all these regular expressions in details.

Vertica REGEXP_SUBSTR

This Vertica regular expression function returns a substring that matches the regular expression within a string value. If there are no matches, then function returns NULL.

Vertica REGEXP_SUBSTR Syntax

REGEXP_SUBSTR( string, pattern [, position [,  occurrence  [, regexp_modifier...  [, captured_subexp ] ] ] ])

Vertica REGEXP_SUBSTR Example

SELECT Regexp_substr(string,
              '\d{6}+')
FROM   (SELECT
'here is my area code 12345 and id that is being used is 389362. These details are collected from employee comments'
AS string) a;

Regexp_substr
---------------
 389362
(1 row)

Vertica Extract Numbers using Regular Expressions

Vertica REGEXP_COUNT

This function searches a string for a regular expression pattern and returns an integer that indicates the number of times the pattern occurs in the string. If no match is found, then the function returns 0.

Vertica REGEXP_COUNT Syntax

REGEXP_COUNT( string, pattern [, position [, regexp_modifier ] ] )

Vertica REGEXP_COUNT Example

SELECT REGEXP_COUNT('a main, a plain, a drain:', 'in');
 REGEXP_COUNT 
--------------
            3
(1 row)

Vertica REGEXP_LIKE

This function is similar to SQL LIKE predicate only difference is, it uses regular expression instead of simple wildcard matching. It will return true if pattern matches string.

Vertica REGEXP_LIKE Syntax

REGEXP_LIKE( string, pattern[,  modifiers ] )

Vertica REGEXP_LIKE Examples

SELECT * 
FROM   (SELECT 'this is 100 sample col1' AS string1 
        UNION 
        SELECT 'this is sample' AS string1) AS db 
WHERE  Regexp_like(string1, '\d');

         string1
-------------------------
 this is 100 sample col1
(1 row)

Vertica REGEXP_NOT_LIKE

This function is similar to SQL NOT LIKE predicate only difference is, it uses regular expression instead of simple wildcard matching. It will return true if pattern does not match string.

Vertica REGEXP_NOT_LIKE Syntax

REGEXP_NOT_LIKE( string, pattern[,  modifiers ] )

Vertica REGEXP_NOT_LIKE Examples

SELECT * 
FROM   (SELECT 'this is 100 sample col1' AS string1 
        UNION 
        SELECT 'this is sample' AS string1) AS db 
WHERE  Regexp_not_like(string1, '\d');

    string1
----------------
 this is sample
(1 row)

Vertica REGEXP_ILIKE

This function is similar to SQL LIKE predicate only difference is, it uses case insensitive regular expression instead of simple wildcard matching. It will return true if pattern matches string.

Vertica REGEXP_ILIKE Syntax

REGEXP_LIKE( string, pattern[,  modifiers ] )

Vertica REGEXP_ILIKE Examples

SELECT * 
FROM   (SELECT 'this is 100 sample col1' AS string1 
        UNION 
        SELECT 'this is Sample' AS string1) AS db 
WHERE  Regexp_ilike(string1, 'Sample');

         string1
-------------------------
 this is 100 sample col1
 this is Sample
(2 rows)

Vertica REGEXP_NOT_ILIKE

This function is similar to SQL LIKE predicate only difference is, it uses case insensitive regular expression instead of simple wildcard matching. It will return true if pattern does not match string.

Vertica REGEXP_NOT_ILIKE Syntax

REGEXP_LIKE( string, pattern[,  modifiers ] )

Vertica REGEXP_NOT_ILIKE Examples

SELECT * 
FROM   (SELECT 'this is 100 sample col1' AS string1 
        UNION 
        SELECT 'this is Sample' AS string1) AS db 
WHERE  Regexp_not_ilike(string1, 'col1');

    string1
----------------
 this is Sample
(1 row)

Vertica REGEXP_INSTR

This function searches a string for a regular expression pattern and returns an integer that indicates the beginning position of the matched substring. If no match is found, then the function returns 0.

Vertica REGEXP_INSTR Syntax

REGEXP_INSTR( string, pattern [, position [, occurrence ... [, return_position [, regexp_modifier ] ... [, captured_subexp ] ] ] ] )

Vertica REGEXP_INSTR Example

SELECT Regexp_instr('This is the in string example', 'in string', 1);

 Regexp_instr
--------------
           13
(1 row)

Vertica REGEXP_REPLACE

This function searches a string for a regular expression pattern and replaces every occurrence of the pattern with the specified replacement string.

Vertica REGEXP_REPLACE Syntax

REGEXP_REPLACE( string, target [, replacement [, position [, occurrence ... [, regexp_modifiers ] ] ] ] )

Vertica REGEXP_REPLACE Example

SELECT Regexp_replace(string, '[a-z/-/A-z/./#/*]', '') 
FROM   (SELECT 
'here is my area code 12345 and id that is being used is 389362 . These details are collected from employee comments'
AS string) a; 

          Regexp_replace
----------------------------------
      12345        389362
(1 row)

Vertica Extract Numbers using Regular Expressions

Hope this helps 🙂