sql - 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 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