Oracle中如何提取字符串内key->Colour对应的value字段值？

你原SQL的问题：INSTR用法搞反了，应该是INSTR(字段名, 要找的字符串)，而且语句存在括号未闭合、SUBSTR参数不全的语法错误，导致执行失败。

要实现反向截取需求，用Oracle的INSTR反向搜索功能就能搞定，完整SQL如下：

SELECT 
    TRIM(SUBSTR(col1, pos_brace + 1, pos_colour - pos_brace - 3)) AS target_value
FROM (
    SELECT 
        col1,
        INSTR(col1, 'key -> Colour') AS pos_colour,
        -- 从key->Colour的位置往前找最近的{
        INSTR(col1, '{', 1, INSTR(col1, 'key -> Colour') * -1) AS pos_brace
    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
) sub;

关键步骤说明： #

1. 定位目标key的位置：用INSTR(col1, 'key -> Colour')获取key -> Colour在字符串中的起始位置。
2. 反向匹配对应块的左括号：INSTR(col1, '{', 1, pos_colour * -1)的第四个参数为负数，表示从key -> Colour的位置从后往前搜索第一个{，精准锁定对应value所在的{}块开头。
3. 截取并清理结果：SUBSTR从{的下一位开始截取，长度设为pos_colour - pos_brace - 3（减3是去掉value与key之间的, ），最后用TRIM去除多余空格，得到干净的目标内容。

执行后会输出你需要的结果：

target_value
-------------
value -> White
value -> Black

内容的提问来源于stack exchange，提问作者Jim Macaulay