SQL Server : RTRIM not working to remove empty spaces

user_3198688 0
Viewed 10648

One of the column values in my tables have empty space at the end of each string.

In my select query, I am trying to trim the empty space at the end of string but the value is not getting trimmed.

SELECT
    EmpId, RTRIM(Designation) AS Designation, City 
FROM
    tblEmployee

This is not trimming the empty space, not just this even the LTRIM(RTRIM(Designation) AS Designation is not working.

I also tried 

CONVERT(VARCHAR(56), LTRIM(RTRIM(Designation))) AS [Designation]

Nothing is trimming the empty space at the end of the string...

Any help appreciated

EDIT

Thanks to suggestions in the comments, I checked what the last value was in the column using ASCII(). It is 160 is a non-breaking space.

How can I remove this non-breaking space?

trim sql-server
3 Answers

How can I remove this non-breaking space?

Just replace it with '' and use CHAR() function as

SELECT REPLACE(YourColumn, CHAR(160), '')
FROM YourTable;
user_64266920

Since the value can contain non-breaking spaces you need to replace those with regular spaces before doing the trim:

SELECT
    EmpId, RTRIM(REPLACE(Designation,char(160),' ')) AS Designation, City 
FROM
    tblEmployee
user_51981400

I faced the similar problem, Use below script to remove the space in case trim function not work -

SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([ColumnName], CHAR(10), 
CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))
FROM [TableName]

For more info visit this page - http://www.ashishblog.com/trim-not-removing-spaces-in-sql/

user_48247190
Related