LTRIM() not removing spaces

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:

Code sql:

---

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 :D

how do you know they're spaces?

after you run your LTRIM update, what does this query give you --

Code:

---

SELECT COUNT(*)
  FROM documents
 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 --

Code:

---

SELECT COUNT(*)
  FROM documents
 WHERE content LIKE CHAR(9)+'%' -- tab

---

Code:

---

SELECT COUNT(*)
  FROM documents
 WHERE content LIKE CHAR(10)+'%' -- line feed

---

Code:

---

SELECT COUNT(*)
  FROM documents
 WHERE content LIKE CHAR(13)+'%' -- carriage return

---

Line feed returned 5500 odd rows.

Should I use something like this to remove them?

Code sql:

---

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!

Quote:

---

Originally Posted by r937

p.s. whaddya bet there's a CHAR(13) sitting right after the CHAR(10)s...

---

Indeed :)