Oracle中如何将test_rows表a_message字段的键值对转为列？

架构老林

2026-7-26

Oracle提取键值对字符串为列的解决方案

我创建了如下Oracle表test_rows，其中a_message字段存储了以键值对形式（如event==''abc''）的字符串数据：

CREATE TABLE test_rows(
id NUMBER(10), 
entity VARCHAR2(15), 
date_s DATE, 
a_message VARCHAR2(1000));

INSERT INTO test_rows 
VALUES(123, 'rat', sysdate, 
'event==''abc'' action==''add_mgmtmeetingdata'' employee_ids==''1;2'' entity==''mgmtmeeting_rat'' meeting_location==''New York'' meeting_type_code==''MSMT'' 
mgmt_meeting_date==''01/27/2010 00:00:00'' source_id==''ABC'' user==''TAM123'' work_object_id==''12345''  user==''TAM345''');

需要将a_message中的指定键值对（event、action、employee_ids、entity、meeting_location、meeting_type_code）提取为独立列，尝试使用REGEXP_SUBSTR和DECODE函数未得到预期结果，期望得到如下结构的查询结果：

event	action	employee_ids	entity	meeting_location	meeting_type_code
abc	add_mgmtmeetingdata	1;2	mgmtmeeting_rat	New York	MSMT

正确的查询语句

可以通过精准的正则匹配提取每个键对应的值，以下是可行的SQL语句：

SELECT
    REGEXP_SUBSTR(a_message, 'event==''([^'']+)''', 1, 1, 'i', 1) AS event,
    REGEXP_SUBSTR(a_message, 'action==''([^'']+)''', 1, 1, 'i', 1) AS action,
    REGEXP_SUBSTR(a_message, 'employee_ids==''([^'']+)''', 1, 1, 'i', 1) AS employee_ids,
    REGEXP_SUBSTR(a_message, 'entity==''([^'']+)''', 1, 1, 'i', 1) AS entity,
    REGEXP_SUBSTR(a_message, 'meeting_location==''([^'']+)''', 1, 1, 'i', 1) AS meeting_location,
    REGEXP_SUBSTR(a_message, 'meeting_type_code==''([^'']+)''', 1, 1, 'i', 1) AS meeting_type_code
FROM test_rows;