Case 3: With Integer Data types:
If integer datatype is used, DATALENGTH() returns 4 and LEN() returns the number of digits.
Example:
DECLARE @Value1 int, @value2 int SET @value1=2 SET @value2 =20000 SELECT DATALENGTH(@value1) SELECT LEN(@value1) SELECT DATALENGTH(@value2)
SELECT LEN(@value2)
The DATALENGTH() returns 4 in both the cases because int always takes 4 bytes, whatever the value would be. But the LEN() treats the integer value as if it were converted to a character type and returns the number of digits. So results are 1 and 5 respectively.
Case 4: With Extra Spaces:
If string or variable value is NULL then both functions returns null.
If the string is having trailing blanks then LEN() function returns the length only up to the last non null character and ignores the spaces. On the otherhand, DATALENGTH() behaves differently returning all along with spaces.
Example:
DECLARE @value1 VARCHAR(25) SELECT @value1 = ‘Dipali ‘
SELECT @value1 AS COL_TEXT, LEN(@value1) AS LENGTH, DATALENGTH(@value1) AS DATA_LENGTH
The result would be 6 and 11. As DATALENTH() takes into account the spaces , so it returns 11 and LEN() returns only the non-null characters.
So these are the similarity and differences between LEN() and DATALENGTH() and according to the requirement we can choose to use them