Function to remove special character from the string

Amatya
Posted by Amatya under Sql Server category on | Points: 40 | Views : 198
Create function dbo.SpecialCharRem(@a varchar(100)) returns varchar(100)
with schemabinding
begin
if @a is null
return null
declare @b varchar(100)
set @b = ''
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l
begin
declare @c int
set @c = ascii(substring(@a, @p, 1))
if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
set @b = @b + char(@c)
set @p = @p + 1
end
if len(@b) = 0
return null
return @b
end


The above code is a function, and we can call this function -


 Select dbo.SpecialCharRem('xyz#890$XYZ')


The output will be -

xyz890XYZ

Thanks for reading.

Comments or Responses

Login to post response