从oracle sql中的字符串中提取特殊字符 [英] extracting special character from a string in oracle sql

问题描述

我需要一个查询来删除字符串中的所有字母数字字符，并只给我特殊字符.

I need a query to remove all alphanumeric characters from a string and give me only special characters.

如果字符串是'@#45gr@@3'，查询应给我'@#@@'.

If string is '@#45gr@@3' query should give me '@#@@'.

推荐答案

使用replace()调用的老式方法:

select translate(upper(txt)
                 , '.1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ'
                 , '.') as spec_txt
from t42
/

使用replace()解决方案最好键入我们要排除的所有字符.是的，那是更大的集合(可能)，但至少它是定义明确的.我们不想继续修改代码以处理输入字符串中的新字符.

With a replace() solution is better to type all the characters we want to exclude. Yes that is the larger set (probably) but at least it's well-defined. We don't want to keep revising the code to handle new characters in the input string.

显然，正则表达式解决方案更紧凑，谁能否认@venkatesh解决方案的优雅?但是，评估正则表达式比专注于SQL的函数要昂贵得多，因此，对于那些存在性能问题的情况，值得一提的是要知道其他的处理方式.

Obviously regex solution is more compact and who can deny the elegance of @venkatesh solution? However, evaluating regular expressions is more expensive than more focused SQL functions, so it's worth knowing alternative ways of doing things, for those cases when performance is an issue.

这篇关于从oracle sql中的字符串中提取特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！