Menu
This is in SQL Server 2005. I have a varchar column and some rows contain trailing space, e.g. abc, def.
I tried removing the trailing space with this command:
update thetable
set thecolumn = rtrim(thecolumn)
But the trailing space remains. I tried to find them using:
select *
from thetable
where thecolumn <> rtrim(thecolumn)
But it returned nothing.
Are there some settings that I am not aware that influences trailing space check?
EDIT:
I know that there is trailing space from SSMS, when I copy paste the value from the grid to the editor, it has trailing space.
341
The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
The most likely culprits are going to be 13 and/or 10 which are carriage return / line feed respectively. There other characters are what is causing the behavior you are seeing. The LTRIM and RTRIM functions are working perfectly fine, it is your data that is at fault here.
To remove leading and trailing spaces in Java, use the trim() method. This method returns a copy of this string with leading and trailing white space removed, or this string if it has no leading or trailing white space.
Python String strip() function will remove leading and trailing whitespaces. If you want to remove only leading or trailing spaces, use lstrip() or rstrip() function instead.
Check if the spaces that are not removed have the ASCII code 32. Try this to replace "hard space" with "soft space":
update thetable set thecolumn = rtrim(replace(thecolumn, char(160), char(32)))
the query was missing equal sign
68
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
