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%'
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%'
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…
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%'
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%'
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%'
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%'
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%'
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%'
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%'
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%'
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 code
Direct characters set | |
Space character in ASCII code | |
One character in ASCII code | |
Multiple characters in ASCII code | |
Using CONCAT with multiple characters in ASCII code | |
Variable with several characters | |
Variable with one character in ASCII code | |
Variable with multiple characters in ASCII code | |
Using CONCAT with a variable with multiple characters in ASCII code |