I wanted to extract all the "name" values from this:
{"id":"1","name":"simpleword","type":"test"},{"id":"123","name":"f23ÜÜ","type":"prod"}
I was presented with the following solution;
regexp_substr_all(s, 'name":"([^"]*)"', 1, 1, '', 1)
and now I am trying to understand the regex. I understand that the
'name":"([^"]*)"'
part extracts the whole part:
"name":"simpleword",
"name":"f23ÜÜ"
but how is the 1, 1, '', 1 working to extract the values of the name keys only?
how is the 1, 1, '', 1 working to extract the values of the name keys only?
They are all explained here: https://docs.snowflake.com/en/sql-reference/functions/regexp_substr_all.html#arguments
Yes, I have already seen the names, but how are they helping in extract the name values?
In your case, position and occurrence are default values, so they are just entered to be able to enter the rest of the parameters. I don't think regex_parameters is important but the group number (last 1) is required otherwise the regexp will not care about grouping (), and will return the whole matching string:
"([^"]*)" this part, i understand that we group everything between the " " but why is there another " inside the square brackets?
REGEXP_SUBSTR_ALLreference. These are<position> [ , <occurrence> [ , <regex_parameters> [ , <group_num> ]args.