How do we remove whitespace from a character string in SQL Server?
It’s easy, just use the LTRIM/RTRIM string manipulation function!
Take the following silly example:
We have a large amount of leading whitespace in our string. To remove that whitespace, we can put this string in the LTRIM system function:
The LTRIM system function removes the leading whitespace and returns the modified string. To prove it, let’s put the resulting string in a DATALENGTH system function, which will return the number of bytes used by a string:
The DATALENGTH function returns the number of bytes used in a string, including whitespace. In standard single-byte encoding, this means one character uses one byte of storage.
We can see that LTRIM removed the leading whitespace of our string, then DATALENGTH returned the length of the modified string. Indeed, the string “Hello” is only 5 characters long!
What about trailing whitespace? Take a look at the following string that uses trailing whitespace:
Once again, DATALENGTH returns the number of bytes used by a string, including whitespace. If we apply the RTRIM function to our string, the trailing whitespace is removed, and we are left with the length of the “Coffee!” string:
Nothin’ to it!
Next Steps:
Leave a comment if you found this quick tutorial helpful!
LTRIM and RTRIM are part of the Top 10 SQL Server string functions you should know!
Take a look at the full tutorial to learn about other extremely helpful string functions you should know.
Also, LTRIM and RTRIM are referenced in the following FREE 1-page guide:
FREE 1-page Simple SQL Cheat Sheet on the Top 10 String Functions you NEED to know!
This guide will definitely be a great resource for you to reference throughout your career as a database professional. Make sure you download it today!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
