Removing unwanted characters
Sometimes
when you import data from other system, you may want to clean up data by removing unwanted characters
You can
use Replace function. Suppose you want to remove the character ^ from the string
declare
@str varchar(100)
set @str='test^ string'
select @str=replace(@str,'^','')Select @str
But what
if you have lot of characters to be removed?
You can have a seperate table that has the set of characters to be removed and use a function
--Create test data
create
table #data (data varchar(100))
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'
--Create table that has all set of characters to be removed
create
table clean_chars (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 '#' union all
select '!'
--Create a function that removes all those characters from data
create
function clean_data
(
@data varchar(100)
)
returns varchar(1000)
as
begin
declare @char_id varchar(1000),@clean_data varchar(1000)
set @clean_data=@data
select @char_id=min(char_id) from clean_chars
while @char_id is not null
begin
select @clean_data=replace(@clean_data,chars,'') from clean_chars where char_id=@char_id
select @char_id= min(char_id) from clean_chars where char_id>@char_id
end
return @clean_data
end
--Run the query
select
data,dbo.clean_data(d.data) as clean_data from #data as d