With the release of SQL Server 2017, a new TRIM() is also introduced which helps to remove the white space/characters from both sides of a string. Before 2017, this functionality was achieved by using the following SQL functions.
- REPLACE - used o replace a character from a string
- LTRIM - trim the white spaces from the left side of a string
- RTRIM - trim the white spaces from the right side of a string
I can explain the functionality with two scenarios.
Scenario 1
Let's assume, we have a string named ' ABC ' and we are going to eliminate the white spaces from both sides of the string.
In SQL, we usually use the LTRIM and RTRIM function like in the code below.
- SELECT LTRIM( RTRIM(' ABC '))
Now, this can be done by using a single TRIM function.
Test results from SSMS can be seen below.
Scenario 2
Assume we have a string named 'X ABC Y' and we need to extract 'ABC' from that. As usual, we will go with the REPLACE function as follows.
- SELECT REPLACE(REPLACE('X ABC Y','X ',''),' Y','')
Here you go with the TRIM function.
- SELECT TRIM('XY ' FROM 'X ABC Y')
Test results from SSMS are shown below.
Note - It is necessary that you have to mention the trailing charter in the TRIM function, otherwise, this will not work as expected.
For example, if you try to remove the 'white space' only from the string 'X ABC Y', then TRIM will not help you. Similarly, if you don't mention the letter 'Y', TRIM will not remove the white space after the string, even though you already mentioned the 'X' and the 'white space' characters inside the TRIM function. See these scenarios in the below screenshot.
Test results from SSMS,