将表格的一个字段分开最多5个单独的行，每个记录分隔符字符的每个实例（char（30））

Question

我目前正在研究一个Oracle SQL脚本，该脚本需要将下表的地址字段拆分下面的地址字段最多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;

输出：

id	address1	address2	address3	address4	address5
1000000	xxxxx xxxxx	xxxxx xxxx	xxxxxx xx xxxxxx	null	null
1000001	61 xxxxxxx xxxx	xxxxxxx	xxxx	null	null
1000002	36 xxxxx xxx	xxxxxxxxx	xxxxxxxxxxxxxx	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx	null
1000003	abc	null	null	def	hij

If you did want to use (slower) regular expressions then:

SELECT id,
       REGEXP_SUBSTR(address, '(.*?)('|| CHR(30) || '|$)', 1, 1, NULL, 1 ) AS address1,
       REGEXP_SUBSTR(address, '(.*?)('|| CHR(30) || '|$)', 1, 2, NULL, 1 ) AS address2,
       REGEXP_SUBSTR(address, '(.*?)('|| CHR(30) || '|$)', 1, 3, NULL, 1 ) AS address3,
       REGEXP_SUBSTR(address, '(.*?)('|| CHR(30) || '|$)', 1, 4, NULL, 1 ) AS address4,
       REGEXP_SUBSTR(address, '(.*?)('|| CHR(30) || '|$)', 1, 5, NULL, 1 ) AS address5
FROM   table_name

Which outputs the same 。

db＆lt;＆gt;＆gt;