Question: I have strings which have a JSON-like format, including:

```
..."id":"500", ..., "id":"600", ...

```

I need to parse the **second** `id` out of the column. I found lots of answers using `substring_index`, however, I need to get the string *after* the 2nd (of potentially n) occurrences and not the string *before* to parse out the ID.

Is there a nice solution?

Answer: 
To find the substring of a column "some\_column" occurring after the nth 
occurrence of a target string,

```
SELECT 
  SUBSTRING(some_column, CHAR_LENGTH(SUBSTRING_INDEX(some_column, <target_string>, <n>)) + <length of target string + 1>)
FROM some_table
-- or if you want to limit the length of your returned substring...
SELECT 
  SUBSTRING(some_column, CHAR_LENGTH(SUBSTRING_INDEX(some_column, <target_string>, <n>)) + <length of target string + 1>, <desired length>)
FROM some_table

```

For this question, the form would be:

```
SELECT 
  SUBSTRING(col, CHAR_LENGTH(SUBSTRING_INDEX(col, '"id":"', 2)) + 7)
FROM `table`

```
In Snowflake, this can be done as follows:

```
select
    , split_part([field_name], '{separator}', {n-counter})
from 
    [table]

```

Note: {separator} and {n-counter} are inputs provided by the user. Snowflake requires apostrophes around {separator}.