根据位置反转子字符串的值

您可以从以usingvalue -> <value>结尾 的开始搜索，然后替换using key -> ColourREGEXP_SUBSTR,key -> ColourREGEXP_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;

小提琴

如果您希望减少对“k -> v”对的顺序的依赖，则需要使用正则表达式进行进一步的解析：

with data(id, str) as (
    SELECT 1, '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' as col1 from dual
    union all
    SELECT 2, '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' as col1 from dual
    union all
    SELECT 3, '[{key -> Fruit, value -> Mango}, {key -> Colour, value -> Black}]' as col1 from dual
),
keyvalues as (
    select id, level as grp,
        regexp_replace(replace(regexp_substr(lst, '[^}]+', 1, level),'{',''), '^,\s+', '')
        as keyvalues
    from (
        select 
            id, regexp_replace(str, '\[(.*)\]', '\1') as lst
        from data 
    )
    connect by id = prior id and level <= regexp_count(lst, '}') and prior sys_guid() is not null
),
pairs as (
    select id, idx, grp, keyval,
        substr(keyval, 1, instr(keyval,' ->') - 1) as key, 
        substr(keyval, instr(keyval,' -> ') + length(' -> ')) as value 
    from (
        select id, level as idx,grp, trim( ' ' from regexp_substr(keyvalues, '[^,]+', 1, level)) as keyval
        from keyvalues
        connect by id = prior id and grp = prior grp and
            regexp_substr(keyvalues, '[^,]+', 1, level) is not null and prior sys_guid() is not null
    )
)
select v.id, v.keyval 
from pairs k
join pairs v on k.id = v.id and k.grp = v.grp and k.idx <> v.idx 
    and v.key = 'value' 
where k.value = 'Colour'
;

您可以使用REGEXP_SUBSTR来查找匹配项：

SELECT REGEXP_SUBSTR(
         col1,
         '\{(key -> Colour, )?(value -> [^},]*?)(, key -> Colour)?\}',
         1,
         1,
         NULL,
         2
       ) AS colour
FROM   tbl

对于样本数据来说：

CREATE TABLE tbl (col1) AS
SELECT EMPTY_CLOB() || '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' from dual union all
SELECT EMPTY_CLOB() || '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' from dual;

输出：

或者，不要尝试编码您自己的存储格式。如果您要使用与 JSON 格式略微相似的格式，那么实际上请使用 JSON 格式，而不是近似格式。

UPDATE tbl
SET col1 = REGEXP_REPLACE(
             col1,
             '\{value -> (.*?), key -> (.*?)\}',
             '{"value": "\1", "key": "\2"}'
           )

然后您可以使用适当的 JSON 解析器：

SELECT j.*
FROM   tbl t
       CROSS APPLY JSON_TABLE(
         t.col1,
         '$[*]'
         COLUMNS (
           key_name VARCHAR2(20) PATH '$.key',
           value    VARCHAR2(20) PATH '$.value'
         )
       ) j
WHERE  key_name = 'Colour'

输出：

或者：

SELECT j.*
FROM   tbl t
       CROSS APPLY JSON_TABLE(
         t.col1,
         '$'
         COLUMNS (
           fruit  VARCHAR2(20) PATH '$[*]?(@.key=="Fruit").value',
           colour VARCHAR2(20) PATH '$[*]?(@.key=="Colour").value'
         )
       ) j

输出：

小提琴