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?

CodePudding user response：

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: