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?
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 .
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With