Link to home
Start Free TrialLog in
Avatar of ukerandi
ukerandiFlag for United Kingdom of Great Britain and Northern Ireland

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('Test-Master," ®+ BAP Reagent')
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

Open in new window


Any idea?
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

The code section

if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122

Open in new window

Is determining which characters to keep.  If you want spaces, dashes, etc. included then just add those ASCII values.

if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 or @c IN (32, 45)

Open in new window


http://www.asciitable.com/
ASKER CERTIFIED SOLUTION
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ukerandi, do you still need help with this question?