SJH
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?
Cheers
r937
2
how do you know they’re spaces?
after you run your LTRIM update, what does this query give you –
SELECT COUNT(*)
FROM documents
WHERE content LIKE ' %'
that’s a space followed by the percent sign
SJH
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
4
try some of these –
SELECT COUNT(*)
FROM documents
WHERE content LIKE CHAR(9)+'%' -- tab
SELECT COUNT(*)
FROM documents
WHERE content LIKE CHAR(10)+'%' -- line feed
SELECT COUNT(*)
FROM documents
WHERE content LIKE CHAR(13)+'%' -- carriage return
SJH
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
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…