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)