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
You can remove special characters from a database field using REPLACE() function. The special characters are double quotes (“ “), Number sign (#), dollar sign($), percent (%) etc.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With