PostgreSQL

How to Use substring() with Regular Expressions in PostgreSQL

You can use regular expressions in the substring() function to extract a string that matches a specified pattern:

Syntax:

substring(string from pattern) -- using POSIX regular expressions
substring(string from pattern for escape_char)  -- using SQL regular expressions

Here is one example that uses POSIX regular expressions to extract any word that has ‘ss’ among its letters:

# select substring('Learning SQL is essential.' from '\w*ss\w*');
 substring 
-----------
 essential

substring() with SQL regular expressions involves three parameters: the string to search, the pattern to match, and a delimiter defined after the for keyword. In the following example we look for a three and then seven letter words that starts with an ‘S’ and ends with an ‘L’:

# select substring('Learning SQL is essential.' from '%#"S_L#"%' for '#');
 substring 
-----------
 SQL
 
# select substring('Do you pronounce it as SQL or SEQUEL?' from '%#"S____L#"%' for '#');
 substring 
-----------
 SEQUEL 

You may refer to the PostgreSQL documentation for more information on regular expression pattern matching.