oracle - 查询多值列

Question

我有一个多值列 MISC_CONTENT，列中有以下字符串：

amount = 7995 ;channel = SXXXN21 ;group_header = NPS099 ;currency = EUR

如何通过 group_header 查找来检索值 NPS099？

标签： oraclelookupmultivalue

Answer 1

我们可以尝试使用REGEXP_SUBSTR：

WITH yourTable AS (
    SELECT 'amount = 7995 ;channel = SXXXN21 ;group_header = NPS099 ;currency = EUR' AS col FROM dual
)

SELECT
    REGEXP_SUBSTR(col, '(^|\s|;)group_header = (.*?)\s*(;|$)', 1,1,NULL,2)
FROM yourTable;