Cannot delete destination space in SQL Server 2005

Question

This is in SQL Server 2005. I have a varchar column, and some rows contain finite space, for example. abc , def .

I tried to remove the trailing space with this command:

 update thetable set thecolumn = rtrim(thecolumn) 

But the finite space remains. I tried to find them using:

 select * from thetable where thecolumn <> rtrim(thecolumn) 

But he did not answer.

Are there any settings that I don’t know that affect the verification of the passed space?

EDIT:

I know that there is a space from SSMS, when I copy paste the value from the grid into the editor, it has a finite space.

Answer 1

Check for spaces that are not removed, ASCII code 32. Try replacing “hard space” with “soft space”:

 update thetable set thecolumn = rtrim(replace(thecolumn, char(160), char(32))) 

there was no equal sign in the request

Answer 2

Are you sure this is a space character (ascii 32)? You may get strange behavior with other "invisible" characters. Try to run

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

and see what you get.

Answer 3

Use this function:

Create the [dbo] function. [FullTrim] (@strText varchar (max)) Returns varchar (max) as the Start

 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

Answer 4

The SQL , CHAR(n) columns on the right are spaces in length.

In addition, string comparison operators (and most functions, too) do not take into account trailing spaces.

 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 "aa" 

Run this query:

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

and see if it finds something.

Answer 5

It sounds like this:

1) Regardless of what you use to view the values, insert the final space (or its appearance - try a fixed-width font, such as Consolas).

2) Column CHAR, not VARCHAR. In this case, the column will be filled with spaces up to the length of the column, for example. inserting "abc" into char (4) will always result in "abc"

3) You somehow do not perform updates, do not update the right column or other form of user error. The update statement itself looks right on the face.

Answer 6

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

In my situation, the problem was the LF and CR characters.

Decision

 DECLARE @test NVARCHAR(100) SET @test = 'Declaration status ' SET @test = REPLACE(REPLACE(@test, CHAR(10), ''), CHAR(13), '')