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屋!