使用 REGULAR EXPRESSION IN SQL 將字串轉換為表，即
A,B,C|D,E,F|I,J,K
輸出是
c1 c2 c3
A B C
D E F
G H I
uj5u.com熱心網友回復：
這是一種選擇（閱讀代碼中的注釋）：
SQL> with
2 test (id, col) as
3 -- sample data
4 (select 1, 'A,B,C|D,E,F|I,J,K' from dual union all
5 select 2, 'X|Y,Z|W' from dual
6 ),
7 temp as
8 -- split pipe-delimited values into rows
9 (select id,
10 regexp_substr(col, '[^|] ', 1, column_value) val
11 from test cross join table(cast(multiset(select level from dual
12 connect by level <= regexp_count(col, '\|') 1
13 ) as sys.odcinumberlist))
14 )
15 -- finally, separate columns
16 select id,
17 regexp_substr(val, '\w ', 1, 1) c1,
18 regexp_substr(val, '\w ', 1, 2) c2,
19 regexp_substr(val, '\w ', 1, 3) c3
20 from temp
21 order by id;
ID C1 C2 C3
---------- ----------------- ----------------- -----------------
1 A B C
1 D E F
1 I J K
2 X
2 Y Z
2 W
6 rows selected.
SQL>
如果總是只有一個以豎線分隔的字串，它會變得更簡單：
SQL> with
2 test (id, col) as
3 -- sample data
4 (select 1, 'A,B,C|D,E,F|I,J,K' from dual
5 ),
6 temp as
7 -- split pipe-delimited values into rows
8 (select id,
9 regexp_substr(col, '[^|] ', 1, level) val
10 from test
11 connect by level <= regexp_count(col, '\|') 1
12 )
13 -- finally, separate columns
14 select id,
15 regexp_substr(val, '\w ', 1, 1) c1,
16 regexp_substr(val, '\w ', 1, 2) c2,
17 regexp_substr(val, '\w ', 1, 3) c3
18 from temp
19 order by id;
ID C1 C2 C3
---------- ----------------- ----------------- -----------------
1 A B C
1 D E F
1 I J K
SQL>
uj5u.com熱心網友回復：
使用字串函式會比正則運算式高效得多：
WITH row_bounds ( value, s_pos, c1_pos, c2_pos, e_pos ) AS (
SELECT value,
1,
INSTR(value, ',', 1, 1),
INSTR(value, ',', 1, 2),
INSTR(value, '|', 1)
FROM table_name
UNION ALL
SELECT value,
e_pos 1,
INSTR(value, ',', e_pos 1, 1),
INSTR(value, ',', e_pos 1, 2),
INSTR(value, '|', e_pos 1)
FROM row_bounds
WHERE e_pos > 0
)
SEARCH DEPTH FIRST BY value SET v_order
SELECT SUBSTR(value, s_pos, c1_pos - s_pos) AS value1,
SUBSTR(value, c1_pos 1, c2_pos - c1_pos - 1) AS value2,
CASE e_pos
WHEN 0
THEN SUBSTR(value, c2_pos 1)
ELSE SUBSTR(value, c2_pos 1, e_pos - c2_pos - 1)
END AS value3
FROM row_bounds;
其中，對于樣本資料：
CREATE TABLE table_name (value) AS
SELECT 'A,B,C|D,E,F|I,J,K' FROM DUAL;
輸出：
VALUE1 VALUE2 VALUE3 一種 乙 C D 乙 F 一世 J 鉀
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/356372.html