oracle 删除oracle sql中的换行符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26883132/
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
Remove newlines in oracle sql
提问by manju
Currently in address column in test table,i have data in following format,
目前在测试表的地址列中，我有以下格式的数据，
12th street
Test avenue
Test_City
12街
测试大道
测试城市
but in the output,i would require it in following format,
但在输出中，我需要以下格式，
12th street Test avenue Test_City.
第 12 街测试大道 Test_City。
Could any one please tell me the query to use to display it in the required manner.
任何人都可以告诉我用于以所需方式显示它的查询。
回答by Cyryl1972
You can try this:
你可以试试这个：
select regexp_replace(your_address,'[[:space:]]',' ') from your_tab;
回答by Olafur Tryggvason
Just strip chr(13) and chr(10) from the string:
只需从字符串中去除 chr(13) 和 chr(10) ：
declare
teststring varchar2 (32767) := ' This is the value
that I chose';
begin
dbms_output.put_line (teststring);
dbms_output.put_line (replace (replace (teststring, chr (13), ''), chr (10), ' '));
end;
The result:
结果：
This is the value
that I chose
This is the value that I chose
Two spaces since I put in two returns in the text.
两个空格，因为我在文本中输入了两个回车符。
回答by Flavio Caduff
Try this:
尝试这个：
translate(' example ', chr(10) || chr(13) || chr(09), ' ')
The above will replace all line breaks (chr(10)), all tabs (chr(09)) and all carriage returns (chr(13)) with a space (' ').
以上将用空格（''）替换所有换行符（chr（10）），所有制表符（chr（09））和所有回车（chr（13））。
Just replace ' example ' with your field name on wish you want to have the characters removed.
只需将“示例”替换为您希望删除字符的字段名称。
