0

i am having data wich consists of special characters but my output should not display that special characters or ascii characters . I need only alphanumerics and spaces allother character should be replaced with null . Please suggest some way to handle this i came to know patindex will work fine but i am not sure whether it will replaces multiple characters from a string 

declare @a char
select @a = 'tHo mas@fi5.com'

my output should be tHo masfi5com
Ramesh
  • 123
  • 2
  • 5
  • 15

1 Answers1

1
DECLARE @str VARCHAR(400)
    Declare @expres as varchar(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'
SET @str = 'tHo mas@fi5.com'
WHILE PATINDEX( @expres, @str ) > 0 
          SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')
print @str
Dilip Kr Singh
  • 1,418
  • 1
  • 18
  • 26
  • Thanks its working fine but i want to do this check for the entire table for nearly 30 columns . if i use this while condition for each column i need to give 30 updates . is there any option to do for entire table at asingle shot – Ramesh Oct 01 '13 at 14:23
  • Hi Ramesh, you can use it like a user function and apply on particular column. – Dilip Kr Singh Oct 03 '13 at 11:57