Removing unwanted characters - Part 2


In my previous post about Removing unwanted characters , I posted a method that used a function

Here is another method that uses Dynamic SQL

declare @data table (data varchar(100))

-- table that has source data

insert @data
select 'tes^@&t %stri)-n!g' data union all
select
'))aaer***********)' union all
select
'&^&&hsdf()' union all
select
'jj&wk' union all
select
')hw*pa' union all
select
'No&@#$53*24,Mai()$n R--!oad'

-- table that has unwanted characters

declare @clean_chars table (char_id int identity(1,1),chars char(1))

insert into @clean_chars (chars)
select '^' as repl union all
select ')' union all
select
'(' union all
select
'&' union all
select
'*' union all
select
'%' union all
select
'@' union all
select
'-' union all
select
',' union all
select '$' union all
select
'#' union all
select
'!'

--T-sql code that removes them

declare @replace varchar(8000),@chars varchar(8000),@sql varchar(8000)

select @chars='',@replace='',@sql=''

select @replace = @replace+'replace(', @chars = @chars+''''+chars+''',''''),' from @clean_chars

set @replace=@replace+'''~!@#'','

set @replace=@replace+substring(@chars,1,LEN(@chars)-1)

select @sql=@sql+'select '''+data+''' as old_data, '+replace(@replace,'~!@#',data)+' as new_data union all ' from @data

set @sql=substring(@sql ,1,LEN(@sql)-10)

exec(@sql)

 

Published 02 December 2009 13:58 by Madhivanan
Filed under: , ,

Comments

# re: Removing unwanted characters - Part 2

09 December 2009 08:46 by sfqfirst

Thanks,you are smart in using 'nest' sql.I want to read your more good articles.

# re: Removing unwanted characters - Part 2

10 December 2009 13:44 by Madhivanan

sfqfirst ,

Thanks for the feedback