Oracle：在换行符上拆分文本字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5721106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Split text field on newline
提问by chris
I need to merge a couple of tables, once of which has a single Address field, the 2nd which as 3 address line fields.
我需要合并几个表，其中一个有一个地址字段，第二个是 3 个地址行字段。
The single address field in the 1st table includes line breaks.
第一个表中的单个地址字段包括换行符。
How can I split this field into 3?
我怎样才能把这个字段分成 3 个？
Update:
更新：
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
原来有些记录有 CHR(10) || CHR(13), 其他 CHR(13) || CHR(10)，还有一些只是 CHAR(13)。无论如何，下面接受的解决方案是有效的。一个有用的方法是
select dump(field) from table;
回答by Bob Jarvis - Reinstate Monica
Assuming that your linebreak character is CHR(10), something like the following should work:
假设你的换行符是 CHR(10)，类似下面的内容应该可以工作：
SELECT TRIM(REGEXP_REPLACE(addr, '(.*)' || CHR(10) || '.*' || CHR(10) || '.*', '')) AS STREET_ADDR,
TRIM(REGEXP_REPLACE(addr, '.*' || CHR(10) || '(.*)' || CHR(10) || '.*', '')) AS CITY,
TRIM(REGEXP_REPLACE(addr, '.*' || CHR(10) || '.*' || CHR(10) || '(.*)', '')) AS STATE
FROM addr_table;
If addr_table is populated using the following statement:
如果使用以下语句填充 addr_table：
INSERT INTO addr_table(addr)
VALUES('12345 MY STREET' || CHR(10) || 'NOWHERESVILLE' || CHR(10) || 'ASTATE');
the above SELECT will return
上面的 SELECT 将返回
STREET_ADDR CITY STATE
12345 MY STREET NOWHERESVILLE ASTATE
Share and enjoy
分享和享受
