snowflake regular expression extract data

user_5496062 0
Viewed 745

I am trying to use snowflake regexp_replace to extract data out of a string

This is my string

I just want the date value, in this case it should be 20210101.

I know how to extract this from normal regular expression with (?<=DATE_ID=)(.*)(?=/), but apparently, snowflake does not recognize question mark:

Status: ERROR
(SQLSTATE: 2201B, SQLCODE: 100048): Invalid regular expression: '(?<=DATE_ID=)(.*)(?=/)', no argument for repetition operator: ?

Does anyone know how to do this in snowflake?

snowflake-cloud-data-platform regex
1 Answers

You can use a pattern with a capturing group:

SELECT REGEXP_SUBSTR(col, 'DATE_ID=([^/]*)', 1, 1, 'e', 1)
SELECT REGEXP_SUBSTR(col, 'DATE_ID=([^/]*)', 1, 1, 'e')

See the regex demo.

Details:

  • DATE_ID= - the text that is matched and consumed
  • ([^/]*) - any zero or more chars other than a / char that is captured into Group 1.

The first two 1 arguments after the pattern are position and occurrence arguments, e tells the engine to return the contents of the first capturing group. So, you may keep the last 1 argument (group number), or you may omit it.

See more about REGEXP_SUBSTR in the documentation.

user_38329700
Related