将字符串转换为以管道分隔和逗号分隔的表

Question

在SQL中使用正则表达式将字符串转换为表即

A,B,C|D,E,F|I,J,K  

输出为

c1    c2   c3
A     B    C
D     E    F
G     H    I

Answer 1

这里有一个选项(读取代码中的注释(：

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>

Answer 2

使用字符串函数将比正则表达式高效得多：

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	VALUE 3
A	B	C
D	E	F
I	J	K