Oracle Regexp 用空格替换 \n、\r 和 \t
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16407135/
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 Regexp to replace \n,\r and \t with space
提问by Tuti Singh
I am trying to select a column from a table that contains newline (NL) characters (and possibly others \n, \r, \t). I would like to use the REGEXP to select the data and replace (only these three) characters with a space, " ".
我正在尝试从包含换行符 (NL) 字符（可能还有其他\n, \r, \t）的表中选择一列。我想使用 REGEXP 来选择数据并用空格“”替换（仅这三个）字符。
回答by APC
No need for regex. This can be done easily with the ASCII codes and boring old TRANSLATE()
不需要正则表达式。这可以使用 ASCII 代码和无聊的旧TRANSLATE()轻松完成
select translate(your_column, chr(10)||chr(11)||chr(13), ' ')
from your_table;
This replaces newline, tab and carriage return with space.
这将用空格替换换行符、制表符和回车符。
TRANSLATE() is much more efficient than its regex equivalent. However, if your heart is set on that approach, you should know that we can reference ASCII codes in regex. So this statement is the regex version of the above.
TRANSLATE() 比它的正则表达式更有效。但是，如果您对这种方法感兴趣，您应该知道我们可以在正则表达式中引用 ASCII 代码。所以这个语句是上面的正则表达式版本。
select regexp_replace(your_column, '([\x0A|\x0B|`\x0D])', ' ')
from your_table;
The tweak is to reference the ASCII code in hexadecimal rather than base 10.
调整是以十六进制而不是基数 10 引用 ASCII 代码。
