sqloracle-database

Reverse Substring the value with the position


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


Solution

  • 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;
    

    Fiddle

    COL1 NEW_VAL
    [{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}] value -> White
    [{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}] value -> Black