Regex not supported on snowflake

Question

I'm trying to extract from snowflake with the below regex:

regexp_substr(col1,'(?<=Sample[^;]?=)(\\d*)(?=;)'),

but it fails with:

Invalid regular expression: no argument for repetition operator: ?

What would an equivalent expression be to make snowflake extract?

Answer 1

So the first positive look behind is wanting ?? (not 100%) "Sample and then not semicolon" then capture "zero or more digits" and then "semicolon"

SELECT 'Sample 123;' as col1
    ,regexp_substr(col1,'Sample[^;](\\d*);',1,1,'e')
;

Anyways the 'e' says to turn on extract sub-matches and the other two parameters are where to start, and which match occurance to return. Thus you lookahead and lookbehinds can just be written normally, and this gives 123 as the answer.

but I also think perhaps you are wanting:

SELECT column1 as col1
    ,regexp_substr(col1,'Sample[^;]?=(\\d*);',1,1,'e')
FROM VALUES 
    ('Sample=123;')  -- match, return 123
    ,('Sample =123;') -- match, return 123
    ,('Sample  =123;') -- no match, return NULL
;

which gives:

COL1	REGEXP_SUBSTR(COL1,'SAMPLE[^;]?=(\D*);',1,1,'E')
Sample=123;	123
Sample =123;	123
Sample  =123;	NULL