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

Published Monday, May 11, 2009 1:49 PM by Madhivanan
Filed under: , ,

Comments

# Removing unwanted characters - Part 2

Wednesday, December 2, 2009 2:09 PM by Madhivanan

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

# Removing unwanted characters - Part 2

Wednesday, December 2, 2009 2:21 PM by SQL Server Transact-SQL (SSQA.net)

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