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