+ Reply to Thread
Results 1 to 3 of 3
  1. #1
    Not Really a Member
    Join Date
    Oct 2001
    Posts
    27,879

    SQL - Cleaning up data

     
    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)

  2. #2
    Not Really a Member
    Join Date
    Oct 2001
    Posts
    27,879
    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
    Helicopters don't fly; they vibrate so much and make so much noise that the earth rejects them.

  3. #3
    Ultimate Member shawshank62's Avatar
    Join Date
    Oct 2002
    Location
    southampton, pa
    Posts
    4,789
    that is a great smilie

    good to see you got your problem solved, btw....

    lmao...smilie

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may post new threads
  • You may post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Recommended Sites: ResellerRatings Store Reviews