ukerandi
asked on
Remove special charactors T-sql
Hi I found the function i have to remove the special characters in the Table. But After i used that function all sentence with out spaces.
Original : select dbo.RemoveSpecialChars('Te st-Master, " ®+ BAP Reagent')
Results after use function below - TestMasterBAPReagent
Expected Results Test-Master BAP Reagent
Any idea?
Original : select dbo.RemoveSpecialChars('Te
Results after use function below - TestMasterBAPReagent
Expected Results Test-Master BAP Reagent
ALTER function [dbo].[RemoveSpecialChars] (@s varchar(256)) returns varchar(4000)
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
Any idea?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ukerandi, do you still need help with this question?
Open in new window
Is determining which characters to keep. If you want spaces, dashes, etc. included then just add those ASCII values.Open in new window
http://www.asciitable.com/