Trailing invisiable characters

[jockey]

I have a database table storing product information.

The table is kept up to date from excell spreadsheets that are imported into SQL then Placed onto the Database table.

For some reason invisible characters are on the end of some field data and this is causing my sql statement to fail.

eg

filed looks like this :
EnviroSponge?, dry,18oz printed bag, 100/ca
but actual contains this
EnviroSponge?, dry,18oz printed bag, 100/ca /ca m
There are square boxes at the end not spaces.

Has anyone seen this before and can the pleases tell ne how to get round this problem.

Many thanx in advance

Jamie

 

[tlbroadbent]

It seems most likely that the characters are carriage returns or line feeds or both. If you want to remove them you can use the REPLACE and CHAR functions.

--remove carriage returns (char(13)) and
--line feeds (char(10)) from data column
Update TableName Set ColumnName = Replace(Replace(ColumnName,char(10),'')char(13),'')
Where charindex(char(10),ColumnName)>0
   Or  charindex(char(13),ColumnName)>0

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm