How to Use substring() with Regular Expressions

SQL Tutorial Home

There are several alternatives in Postgres to extract a substring using a regular expression, in this section we will cover several functions like regexp_matches, regexp_replace and regexp_split_to_array which receives a source string and a pattern with the regular expression. Other alternatives can be the like clause or some operators like “~”, “~*”, “!~” and “!~*”.

The function regexp_matches returns a text array with the substrings matching with the regular expression. If we want all the substrings matching with the regular expression, we need to put a ‘g’ as the third parameter (‘g’ means global). Each matching substring will be in a single element array

select regexp_matches('hello how are you', ‘h[a-z]* ’, ‘g’)  
 as words_starting_with_h
Words_starting_with_h
{hello}
{how}

If we omit the ‘g’ then only the first matching substring will be returned in the array

select regexp_matches('hello how are you', ‘h[a-z]* ’)  as words_starting_with_h
Words_starting_with_h
{hello} 
select regexp_matches('yellow submarine', '[aeiou][a-z][a-z][aeiou]','g') 
 as four_letters
four_letters
{ello}
{ubma}

Postgres provides other interesting functions like regexp_replace, regexp_split_to_table and regexp_split_to_array that uses regular expressions.

We can use regexp_replace function to match a substring with a regular expression, then replace it with another substring:

select regexp_replace('yellow submarine', 'y[a-z]*w','blue');
Regexp_replace
blue submarine

We can use regexp_split_to_table to split a string into several strings using a delimiter defined with a regular expression. Every element will be in a different row.

select regexp_split_to_table('the,quick,brown;fox;jumps', '[,;]') AS subelements
Subelements
The
Quick
Brown
Fox
jumps

Note the regular expression ‘[,;]’ specifies that the delimiter can be a ‘,’ or a ‘;’ . In a similar way we can use the regexp_split_to_array function to split a string into many text elements and put them into an array of text. The delimiter is defined with a regular expression similar to the previous example.

select regexp_split_to_array('the,quick,brown;fox;jumps', '[,;]') AS subelements
subelements
{the, quick, brown, fox, jumps}

IN THIS PAGE