CODEWITHSUNDEEP
sql-servertrim
user3198688
user3198688

Reputation: 305

SQL Server : RTRIM not working to remove empty spaces

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?

Upvotes: 7

Views: 16002

Answers (4)

toly P
toly P

Reputation: 62

None of these will work if your datatype is Varchar. Make sure to change your datatype to Nvarchar. Then use trim.

Upvotes: 0

Brijesh Rana
Brijesh Rana

Reputation: 651

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/

Upvotes: 4

Richardissimo
Richardissimo

Reputation: 5773

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

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

How can I remove this non-breaking space?

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

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

Upvotes: 7

Related Questions