Replace function - special characters

Question

Hi All,

       If my column is encountering special characters I would like to replace with blank. Special characters may vary from record to record. Sometimes it is @ sometimes $,!,,$,^,<,&,<,>,?,",{,},+,_,),(.

Do we have any function same like replace that can replace all special characters, whatever it encounters with required value?

---

- please mark correct answers

Answer 1

Check the following blog for nested REPLACE:

http://www.sqlusa.com/bestpractices2005/administration/datacleaningwithreplace/

You need to list all the characters to be replaced with space.

While it looks frightening, nested REPLACE is actually quite fast.

---

---

Kalman Toth Database & OLAP Architect T-SQL Scripts at sqlusa.com
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 2

Take a look at this article

T-SQL: How to Find Rows with Bad Characters

and check See Also section in that article for more articles on that topic.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 3

create function dbo.SupaReplace(@string varchar(MAX), @badchars varchar(50) = '@$!$^<&<>?"{}+_)(.')
returns varchar(MAX)
as
begin
while LEN (@badchars) > 0 
begin
 set @string = replace(@string,left(@badchars,1),' ')
 set @badchars = right(@badchars,len(@badchars)-1)
end
return @string
end
select dbo.SupaReplace('Stuff and things and @$<>? more stuff', NULL)

Try this on for size, I made the default value your list of bad characters, but feel free to muss with it. You also have the option to specify a list in the second parameter.

hth

Answer 4

Take a look at this article

T-SQL: How to Find Rows with Bad Characters

and check See Also section in that article for more articles on that topic.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Thanks Naomi

---

- please mark correct answers