[SOLVED] LOCATE everything *after* nth occurrence in string

Issue

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?

Solution

For now I have:

SELECT substring_index(
         substr(col, locate('"id":"', col, locate('"id":"', col) + 6) + 6),
         '"',
         1)
FROM table

Would love to see a “nicer” answer 🙂

Answered By – D.R.

Answer Checked By – Mary Flores (BugsFixing Volunteer)