SQL 如何更新或删除 varchar 列中的所有特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27640566/
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
How to update or remove all special characters from a varchar column
提问by Filling The Stack is What I DO
I've messed around and allow users to create company names to where they're creating company names like so: Tom & Marks Ice Cream Shop.
我已经搞砸了，并允许用户在他们创建公司名称的地方创建公司名称，例如：Tom & Marks Ice Cream Shop。
The problem is that the company name has a link for others to click on the see the company's profile and .net is throwing a error stating that dangerous characters are not allowed.
问题是公司名称有一个链接供其他人点击查看公司的个人资料，.net 抛出一个错误，指出不允许使用危险字符。
I'm not worried about being to precise with the company name the user can update it their self when every they notice that the '&' is gone.
我不担心准确使用公司名称，当他们注意到“&”消失时，用户可以自行更新。
How can I update a column in SQL with SQL syntax to where all the special characters are removed and a space is added in the place of the special character?
如何使用 SQL 语法将 SQL 中的列更新为删除所有特殊字符并在特殊字符位置添加空格？
Is there a way to identify in sql if there is a special character in a column value?
有没有办法在sql中识别列值中是否有特殊字符？
UPDATE [BuildingPros].[utbProfessionals]
SET [ProfessionalName] = Replace([ProfessionalName],'some character',' ')
WHERE ProfessionalName =ProfessionalName
回答by HaveNoDisplayName
This is the sample code to find string contains special character or not. You can try this one
这是查找字符串是否包含特殊字符的示例代码。你可以试试这个
DECLARE @MyString VARCHAR(100)
SET @MyString = 'abcdef&&&&ybyds'
IF (@MyString LIKE '%[^a-zA-Z0-9]%')
BEGIN
SET @MyString = Replace(@MyString,'&',' ')
PRINT 'Contains "special" characters'
PRINT @MyString
END
ELSE
BEGIN
PRINT 'Does not contain "special" characters'
PRINT @MyString
END
And your UPDATE query will be like this:-
您的 UPDATE 查询将如下所示：-
UPDATE [BuildingPros].[utbProfessionals]
SET [ProfessionalName] =
(
CASE
WHEN [ProfessionalName] LIKE '%[^a-zA-Z0-9]%'
THEN Replace(REPLACE( ProfessionalName, SUBSTRING( ProfessionalName, PATINDEX('%[~,@,#,$,%,&,*,^,&,%,*,(,)]%', ProfessionalName), 1 ),''),'-',' ')
ELSE [ProfessionalName]
END
)
Or You can create a function to remove special char function then call it under Update statement.
或者您可以创建一个函数来删除特殊字符函数，然后在 Update 语句下调用它。
1) Removes special characters from a string value. 2) All characters except 0-9, a-z and A-Z are removed and 3) the remaining characters are returned.
1) 从字符串值中删除特殊字符。2) 除 0-9、az 和 AZ 之外的所有字符都被删除，3) 返回剩余的字符。
create function dbo.RemoveSpecialChars (@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
then call this function in update statement:-
然后在更新语句中调用此函数：-
UPDATE [BuildingPros].[utbProfessionals]
SET [ProfessionalName] =
(
CASE
WHEN [ProfessionalName] LIKE '%[^a-zA-Z0-9]%'
THEN (SELECT dbo.RemoveSpecialChars(ProfessionalName))
ELSE [ProfessionalName]
END
)
