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"';
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"';
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),'');