-1

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?

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
x89
  • 2,798
  • 5
  • 46
  • 110
  • See [`REGEXP_SUBSTR_ALL` reference](https://docs.snowflake.com/en/sql-reference/functions/regexp_substr_all.html#regexp-substr-all). These are ` [ , [ , [ , ] ` args. – Wiktor Stribiżew Sep 27 '22 at 07:58
  • Yes, I have already seen the names, but how are they helping in extract the name values? How is the occurence and position working here? @WiktorStribiżew – x89 Sep 27 '22 at 08:10

1 Answers1

1

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:

Gokhan Atil
  • 9,278
  • 2
  • 11
  • 24
  • makes sense. about ```"([^"]*)"``` this part, i understand that we group everything between the ```" "``` but why is there another ```"``` inside the square brackets? – x89 Sep 27 '22 at 08:28
  • It says any character but not ". So it takes the words between " characters. – Gokhan Atil Sep 27 '22 at 08:41