Menu
Recently I faced a issue when using len() in a query to find out the length of a query, len() was not counting the trailing spaces in the value. But datalength() is counting the trailing spaces also.
Does this means that if I'm doing some operation which deals with the actual length of the value then I have to use dalalength() over len().
ex: If I need the value of a particular value to be is of 10 character length. i.e. If the value is 3 character length I've to append 7 spaces to it.
646
The LEN function removes trailing spaces not leading spaces. DATALENG function returns the number of bytes occupy in a variable. It also considered the spaces also.
The DATALENGTH() function returns the number of bytes used to represent an expression. Note: The DATALENGTH() function counts both leading and trailing spaces when calculating the length of the expression.
SQL Server LEN() FunctionThe LEN() function returns the length of a string. Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length. Tip: Also look at the DATALENGTH() function.
The LEN function does count spaces at the start of the string when calculating the length of the string. The LEN function will return NULL, if the string is NULL.
Be careful. DATALENGTH returns the number of bytes used, not the number of characters.
189
Yes that's exactly what you must do. If you want to just get number of characters excluding blanks you would use LEN() function, while in all other cases DATALENGTH().
Even LEN() documentation has an information that to get number of bytes to represent the extension you should use DATALENGTH()
Here are the links to MSDN docs:
LEN()
DATALENGTH()
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
