home hardware prices news articles forums photos user reviews
Go Back   Tech Support Forums - TechIMO.com > PC Hardware and Tech > Webmastering and Programming
Join TechIMO for Free!
Register Blogs FAQ Members List Calendar Search Today's Posts Mark Forums Read
Reply Get bargains at  »  Dealighted.com
 
Thread Tools
Currently Active Users: 2300
Discussions: 188,405, Posts: 2,243,657, Members: 232,645
Old October 16th, 2003, 10:51 PM   Digg it!   #1 (permalink)
Ultimate Member
 
Join Date: Oct 2001
Posts: 21,029
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)

vass0922 is online now   Reply With Quote
Old October 16th, 2003, 11:00 PM     #2 (permalink)
Ultimate Member
 
Join Date: Oct 2001
Posts: 21,029
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

vass0922 is online now   Reply With Quote
Old October 16th, 2003, 11:03 PM     #3 (permalink)
Ultimate Member
 
shawshank62's Avatar
 
Join Date: Oct 2002
Location: southampton, pa
Posts: 4,792
Send a message via ICQ to shawshank62 Send a message via AIM to shawshank62
that is a great smilie

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

lmao...smilie

shawshank62 is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Most Active Discussions
Is It Just Me? (2929)
3-days in and no threads about Gaza (161)
Misery Loves Company... (2144)
I think I just killed my computer w.. (24)
CPU wont boot (8)
New Build ( Finally ) (7)
Folderchat Weekday thread (444)
Building a gaming computer advice (5)
RCA 52Inch HDTV wont turn on (5)
Recent Discussions
How to increase my ram? (6)
CPU wont boot (8)
Powerdirector 7 (0)
trouble playing Blu-ray through.. (14)
BATCH FILE WONT OPEN!!! HELP!!! (5)
wishin i could edit my aol prof.. (1)
Problem with boot and motherboa.. (1)
What is L2 Cache??? (1)
Netgear HDX101 Powerline HD Eth.. (4)
Blackberry Storm, Gears of War .. (1)
Core 2 Quad Q9550 system (3)
COWBOOM Ripoff! Used Laptop w/$.. (4)


All times are GMT -4. The time now is 09:16 AM.
TechIMO Copyright 2008 All Enthusiast, Inc.



1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28