假设我有一个结构如下的表，
ID Value Timestamp
1 {A,B,C,D}{E,F,G,H}{I,J,K,L} 12-12-2022
现在想从上面的表中取出数据并存储到下面结构的另一个表中，
ID Value1 Value2 Value3 Value4 Tiemstamp
1 A B C D 12-12-2022
1 E F G H 12-12-2022
1 I J K L 12-12-2022
一种可能性是将字符串函数与xmltable结合使用:
with t(ID, Value, tstamp) as (
select 1, '{A,B,C,D}{E,F,G,H}{I,J,K,L}', date '2022-12-12' from dual union all
select 5, '{PQR,ZXY,C,A7}{P,Q,R,S}{D,C,B,A}', date '2023-02-17' from dual)
select id, tstamp,
substr(cv, 1, instr(cv,',') - 1) va,
substr(cv, instr(cv,',', 1, 1)+1, instr(cv,',', 1, 2) - instr(cv,',',1, 1)-1) vb,
substr(cv, instr(cv,',', 1, 2)+1, instr(cv,',', 1, 3) - instr(cv,',',1, 2)-1) vc,
substr(cv, instr(cv,',', 1, 3)+1) vd
from (select id, tstamp, trim(column_value) cv
from t, xmltable(ltrim(replace(replace(value, '{', ',"'), '}', '"'), ',')) xt);
输出:
ID TSTAMP VA VB VC VD
----- ----------- ------ ------ ------ -------
1 2022-12-12 A B C D
1 2022-12-12 E F G H
1 2022-12-12 I J K L
5 2023-02-17 PQR ZXY C A7
5 2023-02-17 P Q R S
5 2023-02-17 D C B A
可以使用递归查询和简单的字符串函数:
WITH bounds (id, value, timestamp, spos, c1pos, c2pos, c3pos, epos) AS (
SELECT id,
value,
timestamp,
1,
INSTR(value, ',', 1, 1),
INSTR(value, ',', 1, 2),
INSTR(value, ',', 1, 3),
INSTR(value, '}', 1, 1)
FROM table_name
WHERE INSTR(value, '}', 1, 1) > 0
UNION ALL
SELECT id,
value,
timestamp,
epos + 1,
INSTR(value, ',', epos + 1, 1),
INSTR(value, ',', epos + 1, 2),
INSTR(value, ',', epos + 1, 3),
INSTR(value, '}', epos + 1, 1)
FROM bounds
WHERE INSTR(value, '}', epos + 1, 1) > 0
)
SELECT id,
SUBSTR(value, spos + 1, c1pos - spos - 1) AS value1,
SUBSTR(value, c1pos + 1, c2pos - c1pos - 1) AS value2,
SUBSTR(value, c2pos + 1, c3pos - c2pos - 1) AS value3,
SUBSTR(value, c3pos + 1, epos - c3pos - 1) AS value4,
timestamp
FROM bounds;
对于样本数据:
CREATE TABLE table_name (ID, Value, Timestamp) AS
SELECT 1, '{A,B,C,D}{E,F,G,H}{I,J,K,L}', DATE '2022-12-12' FROM DUAL UNION ALL
SELECT 2, '{XYZ,ABC,DEF,PQR}{12345,,34,9876}', SYSDATE FROM DUAL
输出:
时间戳2022-12-12就是