Infrastructure at your Service

Stéphane Haby

SQL Server 2017: TRIM not only a replacement of RTRIM and LTRIM

Last month, I present in the IT-Tage 2017 in Frankfurt am Main, a session about SQL Server 2017 overview.
During my session I made a demo on one of the new string T-SQL Commands: TRIM.
My demo was very simple but I have also 2 very good questions.

Before, I write about these question, I will show you what is TRIM.
This function is a replacement of RTRIM and LTRIM to delete all space character before and after a string.

SELECT Name, RTRIM(LTRIM(Name)) as RLTrim_name, TRIM(Name) as Trim_name 
FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

trim01

As you can see in the result of the query, TRIM() do the same as RTRIM(LTRIM()).

But TRIM can also have a second usage and you can give a set of characters to be deleted and this use case is very useful.

I made an example with this query to delete all A,n & e from my precedent query:

SELECT Name, TRIM('A,n,e' FROM Name)  as Trim_name, TRIM('A,n,e, ' FROM Name)  as Trim_name2
FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

trim02

In the result in the column Trim_name, I haven’t what I expected… The problem is that all name values begin or end with several space characters.
I you look the Trim_name2 column and his associated query, you will see that I add a space character in the special characters’ list.
The first interesting question was:

Can I use the char(xx) to define the character to be deleted?

The ASCII code for a Space is 32 (20 in Hex). I test my query with char(32) like this:

SELECT Name, TRIM(char(32) FROM Name)  as Trim_name 
FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

I run the query…

trim03
Yes, it’s works! I can see that my result is without space character.
Now, I try with another character: char(65) à A

SELECT Name, TRIM(char(32) FROM Name) as Trim_name , TRIM(char(65) FROM Name)  as Trim_name2 
FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

trim04

The result is what I expected with a deleted A at the beginning of the string.
My last test is with both characters together in the same Trim function like this:

SELECT Name, TRIM(char(32),char(65) FROM Name) as Trim_name 
FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

trim05

As you can see, I get an error. Incorrect syntax near the keyword ‘FROM’.

This way is perhaps not the good way and I try another syntax with ‘char(32),char(65)':

SELECT Name, TRIM('char(32),char(65)' FROM Name) as Trim_name

FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

trim06

The result is very strange. The character A at the beginning of the string is deleted but not the space character…

This does not work! :oops:

As suggested by the comment of Jan (thanks to him), I use the CONCAT function with all characters that I want to delete:

SELECT Name, TRIM(CONCAT(char(32), char(65)) FROM Name) as Trim_name 
FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

trim12
Using CONCAT work fine!

Let’s go to the second question
The second question was:

Can I use a variable?

My first test with a variable is very simple with the same characters set that I use in my demo:

DECLARE @characters nvarchar(10);
SET @characters= 'A,n,e, '
SELECT Name, TRIM(@characters FROM Name)  as Trim_name FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

trim08
It’s working fine.
Now, I will try with the ASCII code.

First I try with one characters :

DECLARE @characters nvarchar(10);
SET @characters= char(32)
SELECT Name, TRIM(@characters FROM Name)  as Trim_name FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

trim11

I try with several characters.

DECLARE @characters nvarchar(10);
SET @characters= 'char(32),char(65)'
SELECT Name, TRIM(@characters FROM Name)  as Trim_name FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

trim10
As you can see, the variable with several characters in ASCII code does not work.

I also complete this test with the CONCAT function:

DECLARE @characters nvarchar(10);
SET @characters= CONCAT(char(32), char(65))
SELECT Name, TRIM(@characters FROM Name)  as Trim_name FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

trim13

It’s working fine!

Summary

To finish, I summarize all my tests in a little report:

Using CONCAT with a variable with multiple characters in ASCII codeok

Direct characters set ok
Space character in ASCII code ok
One character in ASCII code ok
Multiple characters in ASCII code nok
Using CONCAT with multiple characters in ASCII code ok
Variable with several characters ok
Variable with one character in ASCII code ok
Variable with multiple characters in ASCII code nok
Using CONCAT with a variable with multiple characters in ASCII code ok

 

 

4 Comments

  • Jan says:

    Hi,

    actually, to me it looks like only the example with TRIM(‘char(32),char(65)’ FROM Name) does not yield the correct results.

    The optional parameter with the characters to trim is only a list of characters. Basically it’s a string where EVERY character is to be replaced. It’s not a comma-separated list.

    So if you tell TRIM to do TRIM( ‘A,n,e, ‘ FROM ‘ Aegon Targaryen ‘) I would expect TRIM to remove all occurences of “A”, “n”, “e” and ” ” from the string (and also all “,” but you didn’t have any in your testcase). Your testcase shows the result “gon Targary”, which is correct.

    The testcase TRIM(‘char(32),char(65)’ FROM ‘ Aegon Targaryen ‘) is the funky one. Here the spaces are NOT removed but the “A” is, even if it occurs in between characters that are not removed, which is plain wrong.

    Even more wrong is, that the “char(65)” is evaluated, since you posted it as a string. This should not be evalutated. This should be TRIM(CONCAT(char(32), char(65)) FROM ‘ Aegon Targaryen ‘) because you need to concatenate the evaluated results of the CHAR function.

    Regards,
    Jan

     
    • Stéphane Haby says:

      Hi jan,
      Thank you for your comment.
      You have right about the testcase TRIM(‘char(32),char(65)’ FROM ‘ Aegon Targaryen ‘).
      I will test the case that you suggest on the next days to complete and correct my post.
      Regards,
      Stéphane

       
  • Jan says:

    By the way: Something is wonky with the images. All images are present, but not in the correct order, so that they do not match the test-case that is presented before them.

     

Leave a Reply


9 − two =

Stéphane Haby
Stéphane Haby

Delivery Manager