Remove trailing empty space in a field content

Question

I am using SQL server MSDE 2000. I have a field called notes of type nvarchar(65).

The content is 'Something ' with an extra space after the content (quotes for clarity) in all the records. I used the following command.

UPDATE TABLE1 
   SET notes = RTRIM(LTRIM(notes))

But it does not work. Is there any alternate way to do it?

Answer 1

Are you sure it's a space and not some other unicode character that just looks like a space?

Answer 2

RTRIM should work for this. Are you sure the character afterwards is actually a space?

Is there a problem with the collation you're using, and the display of the character?

Answer 3

Are you sure the query isn't working? Try:

SELECT TOP 100 '~'+ t.notes +'~'
  FROM TABLE1 t

TOP 100 will limit the results to the first 100 rows, enough to get an idea if there's really a space in the output. If there is, and RTRIM/LTRIM is not removing it - then you aren't dealing with a whitespace character. In that case, try:

UPDATE TABLE1
  SET notes = REPLACE(notes, 
                      SUBSTRING(notes, PATINDEX('%[^a-zA-Z0-9 '''''']%', notes), 1), 
                      '')
WHERE PATINDEX('%[^a-zA-Z0-9 '''''']%', notes) <> 0