在SQL中如何去除特殊字符而不使用循环？

Question

是否有一种在 SQL Server 中剥离特殊字符（仅保留字母数字）的方法，而不需要使用循环 / 自定义函数？

到目前为止，我想到的最好的方法是：

Create Function [dbo].[strip_special](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
    While PatIndex('%[^a-z0-9]%', @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex('%[^a-z0-9]%', @Temp), 1, '')
    Return @TEmp
End

在某些服务器上，我没有创建用户定义函数的权限，因此我希望能够在没有这些权限的情况下实现相同的结果。 我还担心循环的效率/性能问题（尽管我想即使是内置的函数/方法本身也可能使用循环）。

谢谢

- Lee Tickett

---

我有时看到人们建议将字符串分解成类似表格的东西，并加入保留字符的表格。这里有一个你可能感兴趣的讨论。 - Levin Magruder

Answer 1

我假设您有一列需要替换，以下是操作步骤：

 declare @table table(id int, temp varchar(15))


insert @table values(1, 'abc-.123+')
insert @table values(2, '¤%&(abc-.?=&(/#')

;with t1 as
(
select temp a, id from @table
union all
select cast(replace(a, substring(a, PatIndex('%[^a-z0-9]%', a), 1), '') as varchar(15)), id
from t1
where PatIndex('%[^a-z0-9]%', a) > 0
)
select t2.*, t1.a from t1
join @table t2
on t1.id = t2.id
where PatIndex('%[^a-z0-9]%', a) = 0
option (maxrecursion 0)

结果：

id          temp            a
----------- --------------- ---------------
2           ¤%&(abc-.?=&(/# abc
1           abc-.123+       abc123

- t-clausen.dk

Answer 2

如果你想更快地完成，可以使用这个函数。

如果你需要不使用函数的话，可能需要使用游标一行一行地获取并对每一行应用下一个函数的内容。

create function dbo.strip_special(@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

- aF.

---

为什么这个更快？我原本以为你的函数要循环输入中的每个字符，而我的函数只需循环每个非字母数字字符。 - Lee Tickett

你也可以使用你的函数。但在这种情况下，你不应该使用 '%[^a-z0-9]%'，而是应该使用'%[^A-Za-z0-9]%'吧？ - aF.

Answer 3

除了有一堆嵌套的REPLACE语句之外，这是我能想到的最好的方法。 我们有多语言要求，因此将事物剥离为字母数字对于阿拉伯语等语言不起作用。

 DECLARE
    @OrgString  nVarchar(max),
    @Pattern    nvarchar(max)


SET @OrgString = N'~,`,!,@,#,$,%,^,&,*,(,),0-9,_,-,+,=,[,],{,},;,:,",<,>,?,/,\,|حساب "خارج الميز1$انية"'
SET @Pattern = '%[~,`,!,@,#,$,%,^,&,*,(,),0-9,_,''-,+,=,[,{,},;,:,",<,>,?,/,\,|]%'


WHILE PATINDEX( @Pattern, @OrgString ) > 0 
    SET @OrgString = REPLACE( @OrgString, SUBSTRING( @OrgString, PATINDEX( @Pattern, @OrgString ), 1 ), '')
SELECT REPLACE(@OrgString, ']', '') -- Cant workout how to put ] in @Pattern

- KAHartle