查找和替换 SQL 中的所有特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31050086/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 03:46:27 来源:igfitidea点击:
Find and Replace All Special Character in SQL
提问by Sunil Acharya
I want to copy all row in new column with replacing all special character with -. my code is below.
我想复制新列中的所有行，并用 - 替换所有特殊字符。我的代码在下面。
My table design
我的餐桌设计
select * from mycode
UPDATE mycode
SET newName = Replace(myname, '%[^0-9a-zA-Z]%', '-')
It's getting copy with my code but the special character are not replaced
它正在使用我的代码复制，但特殊字符没有被替换
Result
结果
回答by Galma88
Try to create this function
尝试创建此功能
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
and then do your UPDATE
然后做你的 UPDATE
UPDATE mycode
SET newName = dbo.RemoveSpecialChars(mycode)
回答by Mukesh Kalgude
Try this query
试试这个查询
DECLARE @specialchar varchar(15)
DECLARE @getspecialchar CURSOR
SET @getspecialchar = CURSOR FOR
SELECT DISTINCT poschar
FROM MASTER..spt_values S
CROSS APPLY (SELECT SUBSTRING(newName ,NUMBER,1) AS poschar from mycode ) t
WHERE NUMBER > 0
AND NOT (ASCII(t.poschar) BETWEEN 65 AND 90
OR ASCII(t.poschar) BETWEEN 97 AND 122
OR ASCII(t.poschar) BETWEEN 48 AND 57)
OPEN @getspecialchar
FETCH NEXT
FROM @getspecialchar INTO @specialchar
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE mycode
SET newName =Replace(myname,@specialchar,'')
FETCH NEXT
FROM @getspecialchar INTO @specialchar
END
CLOSE @getspecialchar
DEALLOCATE @getspecialchar
