SQL 从 Oracle 字符串中删除特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25329333/
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 Special Characters from an Oracle String
提问by tonyf
From within an Oracle 11g database, using SQL, I need to remove the following sequence of special characters from a string, i.e.
在 Oracle 11g 数据库中，使用 SQL，我需要从字符串中删除以下特殊字符序列，即
~!@#$%^&*()_+=\{}[]:”;'<,>./?
If any of these characters exist within a string, except for these two characters, which I DO NOT want removed, i.e.: "|"and "-"then I would like them completely removed.
如果字符串中存在任何字符，除了这两个字，我不想删除，即："|"与"-"那我想他们完全去除。
For example:
例如：
From: 'ABC(D E+FGH?/IJK LMN~OP'To: 'ABCD EFGHIJK LMNOP'after removal of special characters.
From: 'ABC(D E+FGH?/IJK LMN~OP'To:'ABCD EFGHIJK LMNOP'去除特殊字符后。
I have tried this small test which works for this sample, i.e:
我已经尝试过这个适用于这个样本的小测试，即：
select regexp_replace('abc+de)fg','\+|\)') from dual
but is there a better means of using my sequence of special characters above without doing this string pattern of '\+|\)'for every special character using Oracle SQL?
但是有没有更好的方法来使用我上面的特殊字符序列，而无需'\+|\)'使用 Oracle SQL 为每个特殊字符执行此字符串模式？
回答by Braj
You can replace anything other than letters and space with empty string
您可以用空字符串替换字母和空格以外的任何内容
[^a-zA-Z ]
here is online demo
这是在线演示
As per below comments
根据下面的评论
I still need to keep the following two special characters within my string, i.e. "|" and "-".
我仍然需要在我的字符串中保留以下两个特殊字符，即“|” 和 ”-”。
Just exclude more
只是排除更多
[^a-zA-Z|-]
Note:hyphen -should be in the starting or ending or escaped like \-because it has special meaning in the Character class to define a range.
注意：连字符-应该在开始或结束或转义，\-因为它在 Character 类中具有特殊含义来定义范围。
For more info read about Character Classes or Character Sets
有关字符类或字符集的更多信息，请阅读
回答by Unihedron
Consider using this regex replacement instead:
考虑使用此正则表达式替换：
REGEXP_REPLACE('abc+de)fg', '[~!@#$%^&*()_+=\{}[\]:”;'<,>.\/?]', '')
The replacement will match any character from your list.
替换将匹配您列表中的任何字符。
Here is a regex demo!
这是一个正则表达式演示！
回答by Andie2302
The regex to match your sequence of special characters is:
匹配您的特殊字符序列的正则表达式是：
[]~!@#$%^&*()_+=\{}[:”;'<,>./?]+
回答by user2999951
I feel you still missed to escape all regex-special characters. To achieve that, go iteratively: build a test-tring and start to build up your regex-string character by character to see if it removes what you expect to be removed. If the latest character does not work you have to escape it. That should do the trick.
我觉得你仍然错过了转义所有正则表达式特殊字符。要实现这一点，请反复进行：构建一个测试字符串并开始逐个字符地构建您的正则表达式字符串，以查看它是否删除了您希望删除的内容。如果最新的字符不起作用，则必须对其进行转义。这应该够了吧。
回答by Toolkit
SELECT TRANSLATE('~!@#$%sdv^&*()_+=\dsv{}[]:”;'<,>dsvsdd./?', '~!@#$%^&*()_+=\{}[]:”;'<,>./?',' ')
FROM dual;
result:
结果：
TRANSLATE
-------------
sdvdsvdsvsdd
回答by saubhagya gupta
SQL> select translate('abc+de@fg-hq!m', 'a+-@!', etc.) from dual;
TRANSLATE(
----------
abcdefghqm
