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.

Ready for a modern SQL editor?