Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove trailing empty space in a field content

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?

like image 834
bdhar Avatar asked Dec 08 '09 05:12

bdhar


People also ask

How do I get rid of whitespace trailing?

Type M-x delete-trailing-whitespace to delete all trailing whitespace. This command deletes all extra spaces at the end of each line in the buffer, and all empty lines at the end of the buffer; to ignore the latter, change the variable delete-trailing-lines to nil .


1 Answers

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
like image 125
OMG Ponies Avatar answered Oct 20 '22 14:10

OMG Ponies