New built-in function TRIM() in SQL Server 2017
If you are thinking the new TRIM() function in SQL Server 2017 is just a combination of LTRIM() & RTRIM() functions, then you are wrong :). It’s more than that and we will check it today !
– LTRIM() function is used to truncate all leading blanks, or white-spaces from the left side of the string.
– RTRIM() function is used to truncate all trailing blanks, or white-spaces from the right side of the string.
–> Now, with teh new TRIM() function you can do both, but more than that.
Usage #1: TRIM() function will truncate all leading & trailing blanks from a String:
SELECT TRIM (' Manoj Pandey ') as col1, LTRIM(RTRIM(' Manoj Pandey ')) as col2
Usage #2: Plus it can be used to remove specific characters from both sides of a String, like below:
SELECT TRIM ( 'm,y' FROM 'Manoj Pandey') as col1, TRIM ( 'ma,ey' FROM 'Manoj Pandey') as col2, TRIM ( 'm,a,e,y' FROM 'Manoj Pandey') as col3
Thus with the above query you can see that you can trim characters too, by providing leading & trailing characters, but should be in same sequence as your string is.
Also for Col2 & Col3 we have provided Trimming Characters in 2 different ways, but got the same output.
–> Note: I just mentioned above that the leading & trailing characters should be in same sequence. If you provide in different sequence like below you won’t get desired results.
SELECT 'Manoj Pandey' as st, TRIM ( 'a,n' FROM 'Manoj Pandey') as Col1, TRIM ( 'm,e' FROM 'Manoj Pandey') as Col2, TRIM ( 'm,o,y,e' FROM 'Manoj Pandey') as Col3
Like for Col3 you cannot get rid of middle characters (like ‘o’ and ‘n’) until and unless they become leading or trailing characters.
Hi Manoj,
Second query of usage #2, Column 3 am getting different result.
Instead of ‘anoj Pande’ am getting ‘anoj Pand’.. May i know why ?.
Thank you,
Venkattaramanan V