Analysis of string function len and datalength in SQL Server

LEN: Returns the number of characters (not bytes) of the specified string expression, which does not contain trailing spaces.

Datalength: Returns the number of bytes used to represent any expression.

Example 1: (Same, return result is 5):

1. Select LEN (' Sssss ')
2. Select Datalength (' Sssss ')

Example 2: (not the same, datalength is twice times len):

1. Select LEN (N ' sssss ')
2. Select Datalength (N ' sssss ')

Example 3: (not the same, datalength is twice times more than Len, since Len does not include trailing spaces):

1. Select LEN (N ' sssss ')
2. Select Datalength (N ' sssss ')

Example 4: (Not the same, datalength is twice times Len, because Len does not contain trailing spaces, but contains the head space)

1. Select LEN (N ' sssss ')
2. Select Datalength (N ' sssss ')

Note: Len,datalength is null when the variable is null

1. DECLARE @MyVar VARCHAR (10)
2. SET @MyVar = NULL
3. Select LEN (@MyVar)
4. Select Datalength (@MyVar)

The datalength () function returns a number of bytes used to manage the value, which helps to reveal some interesting differences between different data types. When the varchar type is passed to the datalength () and Len () functions, they return the same value:

DECLARE @Value varchar SET @Value = ' abc ' Select DATALENGTH (@Value) Select LEN (@Value) The return value of these statements is 3. Because the varchar type uses 3 single-byte characters to store a three-character value. However, if you use the nvarchar type to manage values of the same length, you need to take more than one byte:

DECLARE @Value nvarchar SET @Value = ' abc ' Select DATALENGTH (@Value) Select LEN (@Value) the datalength () function returns a value of 6, because each using the UN The character of the Icode character set takes up to 2 bytes. The LEN () function returns a value of 3 because the function returns the number of characters, not the number of bytes. Here is an interesting test: How many bytes will be consumed to store an integer variable with a value of 2? And if you want to store an integer variable with a value of 2 billion, how many bytes will it occupy? Try it:

DECLARE @Value1 int, @Value2 int set @Value1 = 2 Set @Value2 = 2000000000 Select Datalength (@Value1) Select LEN (@Value1 Select Datalength (@Value2) Select LEN (@Value2) In both cases, the DATALENGTH () function returns 4. Because the int type, regardless of the value, always uses 4 bytes. The LEN () function essentially treats integer values as data that has been converted to character, so in this case, it returns 1 and 10, which is the number of digits of the value.

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/Hello_World_wusu/archive/2009/10/14/4667452.aspx

Analysis of string function len and datalength in SQL Server