SUBSTRING (Pattern Match) ​

Extract a substring that matches a POSIX regular expression pattern.

Return the portion of the text that matches the pattern. If the pattern contains capturing groups, then the portion of text that matched the first parenthesized expression is returned.

Return null if there is no match.

See also SUBSTR and SUBSTRING.

Syntax ​

SUBSTRING(string FROM posix_regex_pattern)

posix_regex_pattern

Specify the regular expression to be matched against the input.

The posix_regex_pattern must be a constant string and cannot be a column reference.

Examples ​

The following example selects names from the team table where anything followed by 'City' or 'United' appears in the name.

premdb=# select name, substring(name from 'City|United') 
from team 
where name like '%City' or name like '%United';
      name        | substring 
-------------------+-----------
 Birmingham City   | City
 Bradford City     | City
 Cardiff City      | City
 Coventry City     | City
 Hull City         | City
 Leeds United      | United
 Leicester City    | City
 Manchester City   | City
 Manchester United | United
 Newcastle United  | United
 Norwich City      | City
 Sheffield United  | United
 Stoke City        | City
 Swansea City      | City
 West Ham United   | United
(15 rows)

Pay special attention when using parenthesized expressions as they can match an empty string. If this subexpression is the first capturing group, then substring may return an empty string. Consider using a non-capturing group to avoid this behavior.

yellowbrick_test=# select substring('ab', '(z*)b');
 ?column? 
----------
 
(1 row)

The following example uses a non-capturing group, which changes the result from the previous example. The empty string is matched in the example, but it is not taken into account when returning the result.

yellowbrick_test=# select substring('ab', '(?:z*)b');
 substring 
-----------
 b
(1 row)

Parent topic:SQL Operators and Pattern Matching Functions