embedded control characters

Beyond my control: I am finding control characters (likely tab) is
making its way into address fields of our operational system. This is
messing me up when I load the data into our warehouse w/ BCP (fields
get shifted).
Is the a nifty way to strip control characters from data?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> Beyond my control: I am finding control characters (likely tab) is
> making its way into address fields of our operational system. This is
> messing me up when I load the data into our warehouse w/ BCP (fields
> get shifted).
> Is the a nifty way to strip control characters from data?

UPDATE tbl
SET col = replace(col, char(9), ' ')
WHERE col LIKE '%' + char(9) + '%'

You could have to nest replace, if there are more characters you want
to kill.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp