CODEWITHSUNDEEP
sqlsql-servert-sql
Everton Gomes
Everton Gomes

Reputation: 171

Why am I getting a value with spaces using LTRIM and RTRIM?

Using the LTRIM and RTRIM functions to remove spaces isn't working as I expect:

Example:

SELECT RTRIM(LTRIM('TEST NAME  ')) 
RESULT = 'TEST NAME  ' DOES NOT WORK

SELECT RTRIM(LTRIM('TEST NAME '))
RESULT = 'TEST NAME' IT WORKS

Upvotes: 0

Views: 367

Answers (4)

Zorkolot
Zorkolot

Reputation: 2027

You can remove control characters CHAR(10) and CHAR(13) using replace:

SELECT RTRIM(LTRIM(REPLACE(REPLACE('TEST NAME  ', CHAR(13), '')
                          ,CHAR(10), '')
                  )
            )

Upvotes: 0

irfandar
irfandar

Reputation: 1810

Use this sample code to find any hidden chars in your string

-- The @position variable holds the position of the character currently  
-- being processed. The @nstring variable is the Unicode character   
-- string to process.  
DECLARE @position int, @nstring nvarchar(500);  
-- Initialize the current position variable to the first character in   
-- the string.  
SET @position = 1;  
-- Initialize the character string variable to the string to process.   
-- Notice that there is an N before the start of the string, which   
-- indicates that the data following the N is Unicode data.  
SET @nstring = N'TEST NAME  ';  
-- Print the character number of the position of the string you are at,   
-- the actual Unicode character you are processing, and the UNICODE   
-- value for this particular character.  
PRINT 'Character #' + ' ' + 'Unicode Character' + ' ' + 'UNICODE Value';  
WHILE @position <= DATALENGTH(@nstring)  
-- While these are still characters in the character string,  
   BEGIN;  
   SELECT @position,   
      CONVERT(varchar, SUBSTRING(@nstring, @position, 1)),  
      UNICODE(SUBSTRING(@nstring, @position, 1));  
   SELECT @position = @position + 1;  
   END;

Source https://learn.microsoft.com/en-us/sql/t-sql/functions/unicode-transact-sql

Upvotes: 2

AFract
AFract

Reputation: 9690

Actually for me with SQL Server 2008 R2 it works perfectly for both of your examples.

Proof : 

SELECT '"' + RTRIM(LTRIM('TEST NAME  ')) + '"'  
SELECT '"' + RTRIM(LTRIM('TEST NAME ')) + '"'

Will give "TEST NAME" as output for both.

Your real data probably contain something else than spaces but your sample does not illustrate that.

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175954

Probably you have some characters that are not spaces (tabs) or your string is ending with carriage-return/null-terminated.

Upvotes: 4

Related Questions