Cannot remove trailing space in SQL Server 2005

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 comm开发者_运维知识库and:

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.

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

Are you certain that it is a space (ascii 32) character? You can get odd behavior with other "non-visible" characters. Try running

select ascII(right(theColumn, 1))
 from theTable

and see what you get.

Use this Function:

Create Function [dbo].[FullTrim] (@strText varchar(max)) Returns varchar(max) as Begin

Declare @Ch1 char,@ch2 char
Declare @i int,@LenStr int
Declare @Result varchar(max)
Set @i=1
Set @LenStr=len(@StrText)
Set @Result=''


While @i<=@LenStr
Begin
    Set @ch1=SUBSTRING(@StrText,@i,1)
    Set @ch2=SUBSTRING(@StrText,@i+1,1)
    if ((@ch1=' ' and @ch2=' ') or (len(@Result)=0 and @ch1=' '))

        Set @i+=1

    Else
        Begin
            Set @Result+=@Ch1
            Set @i+=1

        End

End

Return @Result End

In SQL, CHAR(n) columns are right-padded with spaces to their length.

Also string comparison operators (and most functions too) do not take the trailing spaces into account.

DECLARE @t TABLE (c CHAR(10), vc VARCHAR(10))

INSERT
INTO    @t
VALUES  ('a   ', 'a    ')

SELECT  LEN(c), LEN(vc), с + vc
FROM    @t

--
1    1    "a         a"

Please run this query:

SELECT  *
FROM    thetable
WHERE   thecolumn + '|' <> RTRIM(thecolumn) + '|'

and see if it finds something.

It sounds like either:

1) Whatever you are using to view the values is inserting the trailing space (or the appearance thereof- try a fixed-width font like Consolas).

2) The column is CHAR, not VARCHAR. In that case, the column will be padded with spaces up to the length of the column, e.g. inserting 'abc' into char(4) will always result in 'abc '

3) You are somehow not committing the updates, not updating the right column, or other form of user error. The update statement itself looks correct on the face of it.

I had the same issues with RTRIM() AND LTRIM() functions.

In my situation the problem was in LF and CR chars.

Solution

DECLARE @test NVARCHAR(100)
SET @test = 'Declaration status  '

SET @test = REPLACE(REPLACE(@test, CHAR(10), ''), CHAR(13), '')