Strip special characters in SQL without loop?

Question

Is there a way to strip special characters (leaving just alphanumeric) from a string/field in SQL server without a loop / custom function?

So far, the best i've come up with is:

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

On some servers I don't have the privileges to cread user defined functions so i'd like to be able to achieve the same result without. I also have concerns over the efficiency/performance of the loop (although I guess even a built-in function/method would probably itself use a loop).

Thanks

Solution 1

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 @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)

Result:

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

Solution 2

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 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

Comments

[Lee Tickett]

Why is this faster? I would have thought your function has to loop for every character of the input whereas my function will only loop for every non-alphanumeric character?

[aF.]

You can use your funtion as well. But in that case should't you use '%[^A-Za-z0-9]%' instead of '%[^a-z0-9]%'?