REGEXP_EXTRACT ​

Return the matching text item.

When the pattern does not contain capturing groups, the REGEXP_EXTRACT function is similar to the SUBSTRING function.

When the pattern does contain capturing groups, the REGEXP_EXTRACT function will still return the entire matched input while the SUBSTRING function will only return the results of the first capturing group.

Syntax ​

result = REGEXP_EXTRACT(input, pattern  [, start [, reference]] [, flags])

input value

Specify the value against which the regular expression is matched.

pattern value

Specify the regular expression to be matched against the input.

The pattern value must be a constant string and cannot be a column reference.

start value

The position in the input from where the pattern match starts. The default start position is 1.

Yellowbrick does not support special meanings for negative start values.

The start values can be column references.

reference value

Specify which pattern match is of interest. The default value is 1.

The reference values can be column references.

flags value

Can be either i for case-insensitive matching or c for case-sensitive matching. Matching is case-sensitive by default.

The flags value must be a constant string and cannot be a column reference.

Return ​

If the pattern matches the input, the function returns the matched value.

If the pattern does not match the input, the function returns NULL.

Example ​

premdb=> select season_name,REGEXP_EXTRACT(season_name,'(\d+)-(\d+)') from season where numteams=22;
 season_name | regexp_extract
-------------+----------------
 1992-1993   | 1992-1993
 1993-1994   | 1993-1994
 1994-1995   | 1994-1995
(3 rows)

Parent topic:SQL Operators and Pattern Matching Functions