从 SQL Server 查询中消除特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24915215/
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
Eliminating Special Characters From a SQL Server Query
提问by Yanni
Is there a way that i can eliminate all 'special characters' from a SQL Server query? Sometimes our users will put odd things like '@' or '&' in their name fields and I am trying to eliminate anything that is not a number or letter. Is this possible?
有没有办法可以从 SQL Server 查询中消除所有“特殊字符”？有时，我们的用户会在他们的姓名字段中添加诸如“@”或“&”之类的奇怪内容，而我正试图消除任何不是数字或字母的内容。这可能吗？
Thanks in advance.
提前致谢。
采纳答案by Paul Williams
I would use the answer here:
我会在这里使用答案：
How to strip all non-alphabetic characters from string in SQL Server?
如何从 SQL Server 中的字符串中去除所有非字母字符？
If you cannot create a function, you might be able to use the function's code in your query.
如果您无法创建函数，则可以在查询中使用该函数的代码。
回答by Earl G Elliott III
Best way, if possible, is to do this before you even get to SQL Server. SQL Server is not the best tool for string manipulation. RegEx in the middle tier would handle it well or you could potentially use CLR in SQL using RegEx. Unsure on performance of latter, so would need to be tested.
如果可能，最好的方法是在您使用 SQL Server 之前执行此操作。SQL Server 不是字符串操作的最佳工具。中间层的 RegEx 可以很好地处理它，或者您可以使用 RegEx 在 SQL 中使用 CLR。不确定后者的性能，因此需要进行测试。
回答by Matas Vaitkevicius
There is no out-of-the-box solution, however following function should do it:
没有开箱即用的解决方案，但是以下功能应该可以做到：
CREATE FUNCTION dbo.RemoveSpecial (@S VARCHAR(256)) RETURNS VARCHAR(256)
WITH SCHEMABINDING
BEGIN
IF @S IS NULL
RETURN NULL
DECLARE @S2 VARCHAR(256)
SET @S2 = ''
DECLARE @L INT
SET @L = LEN(@S)
DECLARE @P INT
SET @P = 1
WHILE @P <= @L BEGIN
DECLARE @C INT
SET @C = ASCII(SUBSTRING(@S, @P, 1))
IF @C BETWEEN 48 AND 57 OR @C BETWEEN 65 AND 90 OR @C BETWEEN 97 AND 122
SET @S2 = @S2 + CHAR(@C)
SET @P = @P + 1
END
IF LEN(@S2) = 0
RETURN NULL
RETURN @S2
END
UPDATE:
更新：
You could use a chain of REPLACEstatements.
您可以使用一系列REPLACE语句。
SELECT REPLACE([ColumnName]
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
@String,
'9', ''),
'8', ''),
'7', ''),
'6', ''),
'5', ''),
'4', ''),
'3', ''),
'2', ''),
'1', ''),
'0', '') FROM [TableName]
回答by mohan111
DECLARE @str VARCHAR(25)
SET @str = '@#@@#%@mohan2655&$E##'
WHILE PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ), 1 ),''),'-',' ')
SELECT @str
may be this works !!!!as per your requirement
可能是这样的！！！！根据您的要求
