SQL 在 Oracle 中的字符串中修剪空格（新行和制表符空间）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2268860/
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
Trim Whitespaces (New Line and Tab space) in a String in Oracle
提问by Seshan
I need to trim New Line (Chr(13) and Chr(10) and Tab space from the beginning and end of a String) in an Oracle query. I learnt that there is no easy way to trim multiple characters in Oracle. "trim" function trims only single character. It would be a performance degradation if i call trim function recursivelly in a loop using a function. I heard regexp_replace can match the whitespaces and remove them. Can you guide of a reliable way to use regexp_replace to trim multiple tabspaces or new lines or combinations of them in beginning and end of a String. If there is any other way, Please guide me.
我需要在 Oracle 查询中修剪新行（Chr(13) 和 Chr(10) 以及字符串开头和结尾的制表符空间）。我了解到在 Oracle 中没有简单的方法来修剪多个字符。"trim" 函数只修剪单个字符。如果我使用函数在循环中递归调用修剪函数，那将是性能下降。我听说 regexp_replace 可以匹配空格并删除它们。您能否指导使用 regexp_replace 在字符串的开头和结尾修剪多个制表符空间或新行或它们的组合的可靠方法。如果有其他方法，请指导我。
采纳答案by David Mann
How about the quick and dirty translate function?
快速而肮脏的翻译功能怎么样？
This will remove all occurrences of each character in string1:
这将删除 string1 中每个字符的所有出现：
SELECT translate(
translate(
translate(string1, CHR(10), '')
, CHR(13), '')
, CHR(09), '') as massaged
FROM BLAH;
Regexp_replace is an option, but you may see a performance hit depending on how complex your expression is.
Regexp_replace 是一个选项，但根据表达式的复杂程度，您可能会看到性能下降。
回答by Nick Pierpoint
If you have Oracle 10g, REGEXP_REPLACEis pretty flexible.
如果您有 Oracle 10g，则REGEXP_REPLACE非常灵活。
Using the following string as a test:
使用以下字符串作为测试：
chr(9) || 'Q qwer' || chr(9) || chr(10) ||
chr(13) || 'qwerqwer qwerty' || chr(9) ||
chr(10) || chr(13)
The [[:space:]]will remove all whitespace, and the ([[:cntrl:]])|(^\t)regexp will remove non-printing characters and tabs.
该[[:space:]]会移除所有的空格，而([[:cntrl:]])|(^\t)正则表达式将删除非打印字符和标签。
select
tester,
regexp_replace(tester, '(^[[:space:]]+)|([[:space:]]+$)',null)
regexp_tester_1,
regexp_replace(tester, '(^[[:cntrl:]^\t]+)|([[:cntrl:]^\t]+$)',null)
regexp_tester_2
from
(
select
chr(9) || 'Q qwer' || chr(9) || chr(10) ||
chr(13) || 'qwerqwer qwerty' || chr(9) ||
chr(10) || chr(13) tester
from
dual
)
Returning:
返回：
- REGEXP_TESTER_1: "
Qqwerqwerqwerqwerty" - REGEXP_TESTER_2: "
Q qwerqwerqwer qwerty"
- REGEXP_TESTER_1：“
Qqwerqwerqwerqwerty” - REGEXP_TESTER_2：“
Q qwerqwerqwer qwerty”
Hope this is of some use.
希望这有点用。
回答by Marco
This how I would implement it:
这是我将如何实现它：
REGEXP_REPLACE(text,'(^[[:space:]]*|[[:space:]]*$)')
回答by Gary Myers
You could use both LTRIM and RTRIM.
您可以同时使用 LTRIM 和 RTRIM。
select rtrim(ltrim('abcdab','ab'),'ab') from dual;
If you want to trim CHR(13) only when it comes with a CHR(10) it gets more complicated. Firstly, translated the combined string to a single character. Then LTRIM/RTRIM that character, then replace the single character back to the combined string.
如果您只想在带有 CHR(10) 时修剪 CHR(13)，它会变得更加复杂。首先，将组合字符串转换为单个字符。然后 LTRIM/RTRIM 该字符，然后将单个字符替换回组合字符串。
select replace(rtrim(ltrim(replace('abccccabcccaab','ab','#'),'#'),'#'),'#','ab') from dual;
回答by rcp
TRANSLATE (column_name, 'd'||CHR(10)||CHR(13), 'd')
The 'd' is a dummy character, because translate does not work if the 3rd parameter is null.
'd' 是一个虚拟字符，因为如果第三个参数为空，translate 将不起作用。
回答by OMG Ponies
For what version of Oracle? 10g+ supports regexes - see this thread on the OTN Discussion forum for how to use REGEXP_REPLACEto change non-printable characters into ''.
什么版本的Oracle？10g+ 支持正则表达式 -有关如何使用 REGEXP_REPLACE将不可打印字符更改为''.
回答by abrittaf
I know this is not a strict answer for this question, but I've been working in several scenarios where you need to transform text data following these rules:
我知道这不是这个问题的严格答案，但我一直在处理几种需要按照以下规则转换文本数据的场景：
- No spacesor ctrl charsat the beginning of the string
- No spacesor ctrl charsat the end of the string
- Multiple ocurrencies of spacesor ctrl charswill be replaced to a single space
- 字符串开头没有空格或ctrl 字符
- 字符串末尾没有空格或ctrl 字符
- 多次出现的空格或ctrl 字符将被替换为一个空格
Code below follow the rules detailed above:
下面的代码遵循上面详述的规则：
WITH test_view AS (
SELECT CHR(9) || 'Q qwer' || CHR(9) || CHR(10) ||
CHR(13) || ' qwerqwer qwerty ' || CHR(9) ||
CHR(10) || CHR(13) str
FROM DUAL
) SELECT
str original
,TRIM(REGEXP_REPLACE(str, '([[:space:]]{2,}|[[:cntrl:]])', ' ')) fixed
FROM test_view;
ORIGINAL FIXED
---------------------- ----------------------
Q qwer Q qwer qwerqwer qwerty
qwerqwer qwerty
1 row selected.
回答by Jonty Buthello
If at all anyone is looking to convert data in 1 variable that lies in 2 or 3 different lines like below
如果有人希望将数据转换为位于 2 或 3 个不同行中的 1 个变量，如下所示
'Data1
Data2'
And you want to display data as 'Data1 Data2' then use below
并且您想将数据显示为“Data1 Data2”然后在下面使用
select TRANSLATE ('Data1
Data2', ''||CHR(10), ' ') from dual;
it took me hrs to get the right output. Thanks to me I just saved you 1 or 2 hrs :)
我花了几个小时才得到正确的输出。多亏了我，我才为您节省了 1 或 2 小时 :)
回答by reeko
TRIM(BOTH chr(13)||chr(10)||' ' FROM str)
回答by Raghwendra Kumar Mishra
Instead of using regexp_replacemultiple time use (\s)as given below;
而不是使用regexp_replace多次使用(\s)，如下所示；
SELECT regexp_replace('TEXT','(\s)','')
FROM dual;
