I have data
col1
[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]
[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]
and I wanted to extract a value
value -> White
value -> Black
select col1, instr('key -> Colour' in col1), substr(col1, instr('key -> Colour')
from
(SELECT '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' as col1 from dual
union all
SELECT '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' as col1 from dual
) tbl;
I have tried using position and substring, unable to get, any suggestion would be appreciated.
Basically need to search for a value key -> Colour and extract the value by reverse substring till { which will get me the value value -> White and value -> Black
You can start the search with value -> <value>
ending with key -> Colour
using REGEXP_SUBSTR
, and then replace the ,key -> Colour
using REGEXP_REPLACE
select col1,
REGEXP_REPLACE
(REGEXP_SUBSTR
(col1, 'value -> [^,]*, key -> Colour'), ', key -> Colour') AS new_val
from
(SELECT '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' as col1 from dual
union all
SELECT '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' as col1 from dual
) tbl;
COL1 | NEW_VAL |
---|---|
[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}] | value -> White |
[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}] | value -> Black |