Remove Trailing spaces in a string or a column when TRIM/RTRIM does not work

Very often we need to remove trailing spaces from a text in a varchar variable or a column. In that case we use very old T-SQL function "RTRIM" or the newest one in SQL Server 2017, just called "TRIM".

Here is how it works. Just run following script:

DECLARE @Test VARCHAR(100) = 'ABC';

SET @Test += REPLICATE(' ',10);

PRINT 'Here is an example of not trimmed string concatination: ' + CHAR(09) + '"' + @Test + 'DEF"';

PRINT 'Here is an example of trimmed string concatination: ' + CHAR(09) + CHAR(09)+ '"' + RTRIM(@Test) + 'DEF"';

However, sometimes Trimming does not work and that happens if we have some unwanted invisible characters at the end of the string. See that example:

DECLARE @Test VARCHAR(100) = 'ABC';

SET @Test += REPLICATE(' ',10) + CHAR(9);

PRINT 'Here is an example of not trimmed string concatination: ' + CHAR(09) + '"' + @Test + 'DEF"';

PRINT 'Here is an example of trimmed string concatination: ' + CHAR(09) + CHAR(09)+ '"' + RTRIM(@Test) + 'DEF"';

RTRIM function simply did nothing and we know why, because the very last character in a string was not a space, but hidden unwanted symbol.

In our case we already know what that symbol is, but in the real life we have to research it, and here is how we do this:

DECLARE @Test VARCHAR(100) = 'ABC';

SET @Test += REPLICATE(' ',10) + CHAR(9);

SELECT ASCII(RIGHT(@Test,1));

Now, when we know, who is a troublemaker we can simply replace unwanted symbol by an empty space, but in our case we can't do that. Symbol with the code 0x09 represents Tabulation and if we simply replace it we can loose all our "TABs" within our text. So, we have to trim that last character of a text if it is unwanted, and only then we can do TRIM operation:

DECLARE @Test VARCHAR(100) = 'ABC';

SET @Test += REPLICATE(' ',10) + CHAR(9);

SET @Test = IIF(ASCII(RIGHT(@Test,1)) != 9,@Test,LEFT(@Test,LEN(@Test)-1))

PRINT 'Here is an example of not trimmed string concatination: ' + CHAR(09) + '"' + @Test + 'DEF"';

PRINT 'Here is an example of trimmed string concatination: ' + CHAR(09) + CHAR(09)+ '"' + RTRIM(@Test) + 'DEF"';

We trimmed the very last character if it is ASCII code 0x09 and then "RTRIM" function perfectly worked.

I'm pretty sure you might hit a situation when there will be more then one different trailing unwanted invisible symbols in your string column. Depending on what those symbols are you might prefer to use "REPLACE" function like this:

SET @Test = REPLACE(@Test,CHAR(9),'');

But as I mentioned earlier, be careful and do not delete a wanted symbol by a mistake.