regex—在oracle sql中将符号为“|”的单列中的数据拆分为多列

8tntrjer  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(334)

我的列值如下

MERGE|WorkRelationship|||2020/12/31|RESIGNATION|Y||XYZ Limited|12345A

我需要一个sql来划分为多个列

Col1 - MERGE
Col2 - WorkRelationship
col3 - NULL
col4 - NULL
Col5 - 2020/12/31
Col6 - RESIGNATION
Col7 -NULL

就像智者的全部价值。
你能帮我一下吗。

ajsxfq5m

ajsxfq5m1#

一个选择是 regexp_substr() . 以下是一种考虑到分隔字符串中可能存在空槽的方法:

select
    regexp_substr(col, '([^|]*)(\||$)', 1, 1, null, 1) col1,
    regexp_substr(col, '([^|]*)(\||$)', 1, 2, null, 1) col2,
    regexp_substr(col, '([^|]*)(\||$)', 1, 3, null, 1) col3,
    regexp_substr(col, '([^|]*)(\||$)', 1, 4, null, 1) col4,
    regexp_substr(col, '([^|]*)(\||$)', 1, 5, null, 1) col5,
    regexp_substr(col, '([^|]*)(\||$)', 1, 6, null, 1) col6,
    regexp_substr(col, '([^|]*)(\||$)', 1, 7, null, 1) col7
from mytable

db小提琴演示:

with mytable as (select 'MERGE|WorkRelationship|||2020/12/31|RESIGNATION|Y||XYZ Limited|12345A' col from dual)
select
    regexp_substr(col, '([^|]*)(\||$)', 1, 1, '', 1) col1,
    regexp_substr(col, '([^|]*)(\||$)', 1, 2, '', 1) col2,
    regexp_substr(col, '([^|]*)(\||$)', 1, 3, '', 1) col3,
    regexp_substr(col, '([^|]*)(\||$)', 1, 4, '', 1) col4,
    regexp_substr(col, '([^|]*)(\||$)', 1, 5, '', 1) col5,
    regexp_substr(col, '([^|]*)(\||$)', 1, 6, '', 1) col6,
    regexp_substr(col, '([^|]*)(\||$)', 1, 7, '', 1) col7
from mytable
COL1  | COL2             | COL3 | COL4 | COL5       | COL6        | COL7
:---- | :--------------- | :--- | :--- | :--------- | :---------- | :---
MERGE | WorkRelationship | null | null | 2020/12/31 | RESIGNATION | Y
nfg76nw0

nfg76nw02#

如果您要拆分的不仅仅是一个字符串,而且它包含数量可变的子字符串,这可能会有所帮助:

SQL> with test (col) as
  2    (select 'MERGE|WorkRelationship|||2020/12/31|RESIGNATION|Y||XYZ Limited|12345A' from dual
  3     union all
  4     select 'Little|Foot' from dual
  5    )
  6  select rtrim(regexp_substr(col, '[^|]*\|?', 1, column_value), '|') val
  7  from test cross join
  8    table(cast(multiset(select level from dual
  9                        connect by level <= regexp_count(col, '\|') + 1
 10                       ) as sys.odcinumberlist))
 11  order by col, column_value;

VAL
------------------------------
Little
Foot
MERGE
WorkRelationship

2020/12/31
RESIGNATION
Y

XYZ Limited
12345A

12 rows selected.

SQL>

相关问题