Skip to main content

REGEXP_SUBSTR

Returns a substring from an input string matching a specified pattern.

See also:
[SELECT] [ Regular Expression Reference ]

Syntax​

SELECT REGEXP_SUBSTR(source, pattern) FROM <multipartIdentifier>;

Arguments​

source
A literal string or string column from which you want to extract a substring based upon a pattern defined by a regular expression (pattern).

pattern
The regular expression pattern to search for.

Return Value(s)​

Returns a string.

Examples​

Extract a substring based upon a regular expression​

select name,
json_extract(properties, '$.storageProfile.imageReference.offer') as os,
regexp_substr(
  json_extract(properties, '$.storageProfile.imageReference.exactVersion'), 
  '[0-9][0-9]\.[0-9][0-9]') as os_version 
from azure.compute.virtual_machines 
WHERE subscriptionId = '273769f6-545f-45b2-8ab8-2f14ec5768dc';

For more information, see https://github.com/nalgeon/sqlean/blob/main/docs/re.md