How to grab certain value found in a string column?

I have a column that contains several different values. This is not in JSON format. It is a string that is separated into different sections. I need to grab everything that is found under ID only.

In the examples below, I only want to grab the word: “syntax” and “village”

select value.id
from TBL_A

The above does not work since this is not a json.

Does anyone know how to grab the full word that is found under the “id” section in that string column?

Answer

Even though it’s a string, since it’s in properly formatted JSON you can convert the string to a JSON variant like this:

select parse_json(VALUE);

You can then access its properties using standard colon and dot notations:

select parse_json(VALUE):id::string