Strip special characters in SQL without loop? Strip special characters in SQL without loop? sql sql

Strip special characters in SQL without loop?


I assume you have a column that you want replaced, this is how you could do it:

 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 @tableunion allselect cast(replace(a, substring(a, PatIndex('%[^a-z0-9]%', a), 1), '') as varchar(15)), idfrom t1where PatIndex('%[^a-z0-9]%', a) > 0)select t2.*, t1.a from t1join @table t2on t1.id = t2.idwhere PatIndex('%[^a-z0-9]%', a) = 0option (maxrecursion 0)

Result:

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


If you want to do it faster, use this function.

If you need to use it without a function, you may need to use cursors to fetch each row at a time and apply the content of the next function for each row.

create function dbo.strip_special(@s varchar(256)) returns varchar(256)   with schemabindingbegin   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


Other than having a great pile of nested REPLACE statements this is the best I could come up with.We have multi-lingual requirement so stripping things back to Alpha-numeric does not work for languages like Arabic

 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