SQL - Cleaning up data

**vass0922** · October 16th, 2003, 09:51 PM

Ok I have a table I'm trying to clean up some pathetic imports.

We export DHCP tables to a text file every night and then import them into a DB for security tracking.

Now one of the columns MAC address has something funky in it I can't identify, but it seems to cause wrapping of the text

I've tried LTRIM RTRIM doesn't remove it
I've tried REPLACE(columnname, CHAR(13), '') to try to replace carriage returns in the string.. that didn't work either

HOW can I identify this mysterious white space!?!!

Its REALLY P'ing me off!! lol
makes bad data

Before I was avoiding the problem by just selecting LEFT(mac, 12) .. but when there is no mac address the same problem occurs

I've also tried CHAR(10) for line feed, and CHAR(9) for tab

Thoughts?

T-SQL
SQL Server 2000
believe datatype is varchar(20)

**vass0922** · October 16th, 2003, 10:00 PM

HA! I am SQL GOD!

Ok.. I can wish

Just dawned on me to try both at the same time

REPLACE(replace(mac, CHAR(10), ''), CHAR(13), '')

To get rid of CarriageReturn/LineFeed combos ..

WOohoo