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?
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
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.
try some of these --
WHERE content LIKE CHAR(9)+'%' -- tab
WHERE content LIKE CHAR(10)+'%' -- line feed
WHERE content LIKE CHAR(13)+'%' -- carriage return
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)+'%';
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...
Ah go on then
Thanks for your help!
This topic is now archived. It is frozen and cannot be changed in any way.