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! 😳

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