Oracle Regexp 用空格替换 、 和 [英] Oracle Regexp to replace , and with space

问题描述

我试图从包含换行符 (NL) 的表中选择一列(可能还有其他 、 、 ).我想使用 REGEXP 来选择数据并用空格"替换(仅这三个)字符.

I am trying to select a column from a table that contains newline (NL) characters (and possibly others , , ). I would like to use the REGEXP to select the data and replace (only these three) characters with a space, " ".

推荐答案

不需要正则表达式.这可以通过 ASCII 代码和无聊的旧 TRANSLATE() 轻松完成

No need for regex. This can be done easily with the ASCII codes and boring old TRANSLATE()

select translate(your_column, chr(10)||chr(11)||chr(13), '    ')
from your_table;

这将用空格替换换行符、制表符和回车符.

This replaces newline, tab and carriage return with space.

TRANSLATE() 比它的正则表达式更有效.但是，如果您决定采用这种方法，您应该知道我们可以在正则表达式中引用 ASCII 代码.所以这个语句是上面的正则表达式版本.

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.

select regexp_replace(your_column,  '([x0A|x0B|`x0D])', ' ')
from your_table;

调整是以十六进制而不是基数 10 引用 ASCII 代码.

The tweak is to reference the ASCII code in hexadecimal rather than base 10.

这篇关于Oracle Regexp 用空格替换 、 和 的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！