oracle 删除特殊字符 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8438362/
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
Removing special character SQL
提问by Femme Fatale
I have column in a table which contains special characters with an attached string. The same column contains numbers also. I am only intresed in extracting numbers out of that column e.g
我在表中有列，其中包含带有附加字符串的特殊字符。同一列也包含数字。我只对从该列中提取数字感兴趣，例如
Name-3445 => 3445; Out-90 => 90; 786 => 786
How would i do this in SQL or PL/SQL?
我将如何在 SQL 或 PL/SQL 中执行此操作？
回答by a_horse_with_no_name
SELECT regexp_replace(some_column, '[^0-9]*', '') as clean_value
FROM your_table
回答by A.B.Cade
You can use the regexp_substr function: http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions116.htm
您可以使用 regexp_substr 函数：http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions116.htm
回答by Scott Hunter
PL/SQL has a REGEX_REPLACE function, which you could use to replace anything that isn't a digit with an empty string. Details on REGEX_REPLACE can be found here: http://psoug.org/reference/regexp.html
PL/SQL 有一个 REGEX_REPLACE 函数，你可以用它来用空字符串替换任何不是数字的东西。可以在此处找到有关 REGEX_REPLACE 的详细信息：http://psoug.org/reference/regexp.html
回答by Matt M
Without knowing the integrity of your data, something like this might do what you're asking:
在不知道数据完整性的情况下，这样的事情可能会满足您的要求：
select CAST(SUBSTRING(_COLUMNNAME_,CHARINDEX('-', _COLUMNNAME_),1000), Integer) as ColumnName
from tblTable where _COLUMNNAME_ like '%-%'
union all select CAST(_COLUMNNAME, Integer) as ColumnName
from tblTable where _COLUMNNAME_ not like '%-%'
回答by karunakar
REGEXP_REPLACE(<Your_String>,'[^[:alnum:]'' '']', NULL)
Example:
例子：
SELECT REGEXP_REPLACE('##$$3&&!!__!','[^[:alnum:]'' '']', NULL) FROM dual;
Output:
输出：
123
