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:


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() 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 '#');

# select substring('Do you pronounce it as SQL or SEQUEL?' from '%#"S____L#"%' for '#');

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


From PostgreSQL query to chart to Slack in seconds

Get to answers faster, together, with PopSQL and PostgreSQL