sjh — 2009-10-14T11:00:47-04:00 — #1
I've got a rather arduous data import/conversion task to complete with a huuuuge table in SQL Server 2005.
There is a column called content, of which many values are preceded by one or more spaces. I tried running the following query to remove these values:
UPDATE documents SET content = LTRIM(content);
It comes back with 9500 rows affected but the query has not affected a single row: there are still spaces at the start of many of them.
I hope this describes the problem adequately. Is someone able to help me out here?
r937 — 2009-10-14T11:10:42-04:00 — #2
how do you know they're spaces?
after you run your LTRIM update, what does this query give you --
WHERE content LIKE ' %'
that's a space followed by the percent sign
sjh — 2009-10-14T11:15:44-04:00 — #3
That query returns a big fat "0"
Is there any way of finding out what that character is?
Edit: ... and more importantly, how to remove it.
r937 — 2009-10-14T11:28:56-04:00 — #4
try some of these --
WHERE content LIKE CHAR(9)+'%' -- tab
WHERE content LIKE CHAR(10)+'%' -- line feed
WHERE content LIKE CHAR(13)+'%' -- carriage return
sjh — 2009-10-14T11:36:09-04:00 — #5
Line feed returned 5500 odd rows.
Should I use something like this to remove them?
UPDATE documents SET content = SUBSTRING(content, 2, LEN(CONTENT)) WHERE content LIKE CHAR(10)+'%';
r937 — 2009-10-14T11:47:25-04:00 — #6
yeah, that should work, and of course you will back up your table before testing it, yeah?
p.s. whaddya bet there's a CHAR(13) sitting right after the CHAR(10)s...
sjh — 2009-10-14T11:48:01-04:00 — #7
Ah go on then
Thanks for your help!
sjh — 2009-10-14T12:02:20-04:00 — #8