LTRIM() not removing spaces


#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 smiley


#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


#3

That query returns a big fat "0" smile

Is there any way of finding out what that character is?

Edit: ... and more importantly, how to remove it.


#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

#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)+'%';

#6

yeah, that should work, and of course you will back up your table before testing it, yeah?

smile

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


#7

Ah go on then smile

Thanks for your help!


#8

Indeed smile


#9