By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
val |
---|
[{"confidence":99,"iso_code":"NJ","geoname_id":5101760,"names":{"en":"New Jersey","es":"Nueva Jersey","fr":"New Jersey","ja":"u30cbu30e5u30fcu30b8u30e3u30fcu30b8u30fcu5dde","pt-BR":"Nova Ju00e9rsia","ru":"u041du044cu044e-u0414u0436u0435u0440u0441u0438","zh-CN":"u65b0u6cfdu897fu5dde"}}] |
REPLACE(SUBSTRING_INDEX(SUBSTRING(vaL,LOCATE('"iso_code":"',val) + 11),',',1),'"','') |
---|
NJ |