Oracle Regexp将\ n，\ r和\ t替换为空格 [英] Oracle Regexp to replace \n,\r and \t with space

问题描述

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

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, " ".

推荐答案

无需正则表达式.使用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将\ n，\ r和\ t替换为空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！