Regex - Invalid regular expression: '(?:[v=)', no argument for repetition operator:?

user_14444816 0
Viewed 2039

I have this query that I'm using in Snowflake:

Select *,
case  WHEN REGEXP_SUBSTR(NAME, '(?:\[v=)') is not null THEN REGEXP_SUBSTR(NAME, '[[]v=([0-9]+)')
else null
end
from my_table;

but when I try to run it, it tells me:

Invalid regular expression: '(?:[v=)', no argument for repetition operator: ?

but I've tested this out on regex101 here and it looks like its working, I want to check for [v=

Edit: More insight into what I'm trying to find w. the regex, I have rows that look like this test [v=123], words [v=444], more [v=532] and I need to be able to look through each column and find if it has [v= and extract the numbers only

snowflake-cloud-data-platform sql regex
2 Answers

You can use

SELECT *, REGEXP_SUBSTR(Name, '[[]v=([0-9]+)', 1, 1, 'e') from my_table;

Here,

  • [[]v=([0-9]+) matches [v= and then captures one or more digits into Group 1
  • 1, 1, 'e' means that we start matching from the first char, one occurrence, and the e makes the engine fetch the Group 1 value (it is default, if you needed Group 2 (if you had it in the pattern) value, you woul add another param, and use REGEXP_SUBSTR(Name, '[[](v|V)=([0-9]+)', 1, 1, 'e', 2)).
user_38329700

This should do it:

SELECT REGEXP_SUBSTR(Name, 'v=([0-9]+)', 1, 1, 'e') val from values ('foo'), ('test [v=111] ') , (' anothertest [v=222]') t(Name) ;

VAL
null
111
222
user_20199780
Related