Oracle:在换行符上拆分文本字段 [英] Oracle: Split text field on newline

问题描述

我需要合并几个表，其中的一个表只有一个地址"字段，第二个表则是3个地址行字段.

I need to merge a couple of tables, once of which has a single Address field, the 2nd which as 3 address line fields.

第一个表中的单个地址字段包含换行符.

The single address field in the 1st table includes line breaks.

如何将该字段分为3个?

How can I split this field into 3?

更新:

结果显示某些记录具有CHR(10)|| CHR(13)，其他CHR(13)|| CHR(10)，还有其他只是CHAR(13).无论如何，以下公认的解决方案均有效.有用的判断方法是

Turns out some records had CHR(10) || CHR(13), others CHR(13) || CHR(10), and yet others just CHAR(13). Regardless, the accepted solution below works. A useful way to tell is

select dump(field) from table;

推荐答案

假定您的换行符为CHR(10)，则应执行以下操作:

Assuming that your linebreak character is CHR(10), something like the following should work:

SELECT TRIM(REGEXP_REPLACE(addr, '(.*)' || CHR(10) || '.*' || CHR(10) || '.*', '\1')) AS STREET_ADDR,
       TRIM(REGEXP_REPLACE(addr, '.*' || CHR(10) || '(.*)' || CHR(10) || '.*', '\1')) AS CITY,
       TRIM(REGEXP_REPLACE(addr, '.*' || CHR(10) || '.*' || CHR(10) || '(.*)', '\1')) AS STATE
      FROM addr_table; 

如果使用以下语句填充addr_table:

If addr_table is populated using the following statement:

INSERT INTO addr_table(addr)
VALUES('12345 MY STREET' || CHR(10) || 'NOWHERESVILLE' || CHR(10) || 'ASTATE');

上面的SELECT将返回

the above SELECT will return

STREET_ADDR     CITY            STATE
12345 MY STREET NOWHERESVILLE   ASTATE

分享并享受

这篇关于Oracle:在换行符上拆分文本字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！