分裂一个字段的表5独立的行记录分隔符的每个实例(CHAR(30))

Question

我目前正在研究一个Oracle SQL脚本，需要将下面表的ADDRESS字段分割为5个单独的行。表示字段分隔符分隔的记录(CHAR(30)。我想问一下，有什么建议的方法来得出上述数据吗?

请参见下面的示例数据。

| ID      | ADDRESS                                                |
|;--------|;-------------------------------------------------------|
| 1000000 | Xxxxx XxxxxXxxxx XxxxXxxxxx xx Xxxxxx                |
| 1000001 | 61 Xxxxxxx XxxxXxxxxxxXxxx                           |
| 1000002 | 36 Xxxxx XxxXxxxxxxxxXxxxxxxxxxxxxxXxxxxxxxxxxxxxxx |

上述示例的预期输出如下:

| ID      | ADDRESS1        | ADDRESS2   | ADDRESS3         | ADDRESS4         | ADDRESS5|
|;--------|;----------------|;-----------|;-----------------|;-----------------|;--------|
| 1000000 | Xxxxx Xxxxx     | Xxxxx Xxxx | Xxxxxx xx Xxxxxx |                  |         |
| 1000001 | 61 Xxxxxxx Xxxx | Xxxxxxx    | Xxxx             |                  |         |
| 1000002 | 36 Xxxxx Xxx    | Xxxxxxxxx  | Xxxxxxxxxxxxxx   | Xxxxxxxxxxxxxxxx |         |

Answer 1

这里有一个选项:

SQL> with test (id, address) as
2    (select 1000000, 'Xxxxx Xxxxx^^Xxxxx Xxxx^^Xxxxxx xx Xxxxxx' from dual union all
3     select 1000002, '36 Xxxxx Xxx^^Xxxxxxxxx^^Xxxxxxxxxxxxxx^^Xxxxxxxxxxxxxxxx' from dual
4    )
5  select id, regexp_substr(address, '[^' ||chr(30) ||']+', 1, 1) val1,
6             regexp_substr(address, '[^' ||chr(30) ||']+', 1, 2) val2,
7             regexp_substr(address, '[^' ||chr(30) ||']+', 1, 3) val3,
8             regexp_substr(address, '[^' ||chr(30) ||']+', 1, 4) val4,
9             regexp_substr(address, '[^' ||chr(30) ||']+', 1, 5) val5
10  from test;
ID VAL1                 VAL2                 VAL3                 VAL4                 VAL5
---------- -------------------- -------------------- -------------------- -------------------- --------------------
1000000 Xxxxx Xxxxx          Xxxxx Xxxx           Xxxxxx xx Xxxxxx
1000002 36 Xxxxx Xxx         Xxxxxxxxx            Xxxxxxxxxxxxxx       Xxxxxxxxxxxxxxxx
SQL>

Answer 2

您可以使用简单的字符串函数(比正则表达式快一个数量级):

SELECT id,
CASE sep1
WHEN 0 THEN address
ELSE        SUBSTR(address, 1, sep1 - 1)
END AS address1,
CASE 
WHEN sep1 = 0 THEN NULL
WHEN sep2 = 0 THEN SUBSTR(address, sep1 + 1)
ELSE               SUBSTR(address, sep1 + 1, sep2 - sep1 - 1)
END AS address2,
CASE 
WHEN sep2 = 0 THEN NULL
WHEN sep3 = 0 THEN SUBSTR(address, sep2 + 1)
ELSE               SUBSTR(address, sep2 + 1, sep3 - sep2 - 1)
END AS address3,
CASE 
WHEN sep3 = 0 THEN NULL
WHEN sep4 = 0 THEN SUBSTR(address, sep3 + 1)
ELSE               SUBSTR(address, sep3 + 1, sep4 - sep3 - 1)
END AS address4,
CASE 
WHEN sep4 = 0 THEN NULL
ELSE               SUBSTR(address, sep4 + 1)
END AS address5
FROM   (
SELECT id,
address,
INSTR(address, CHR(30), 1, 1) AS sep1,
INSTR(address, CHR(30), 1, 2) AS sep2,
INSTR(address, CHR(30), 1, 3) AS sep3,
INSTR(address, CHR(30), 1, 4) AS sep4
FROM   table_name
)

对于样本数据:

CREATE TABLE table_name (ID, ADDRESS) AS
SELECT 1000000, 'Xxxxx Xxxxx'||CHR(30)||'Xxxxx Xxxx'||CHR(30)||'Xxxxxx xx Xxxxxx' FROM DUAL UNION ALL
SELECT 1000001, '61 Xxxxxxx Xxxx'||CHR(30)||'Xxxxxxx'||CHR(30)||'Xxxx' FROM DUAL UNION ALL
SELECT 1000002, '36 Xxxxx Xxx'||CHR(30)||'Xxxxxxxxx'||CHR(30)||'Xxxxxxxxxxxxxx'||CHR(30)||'Xxxxxxxxxxxxxxxx' FROM DUAL UNION ALL
SELECT 1000003, 'ABC'||CHR(30)||CHR(30)||CHR(30)||'DEF'||CHR(30)||'HIJ' FROM DUAL;

输出:ADDRESS5空Xxxxxxx空空HIJ