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.