Comparison of string functions Len and datalength in SQL Server

Len: returns the number of characters (not bytes) of the specified string expression, excluding trailing spaces.

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

Example 1: (same, the returned result is 5 ):

1. Select Len ('ssss ')
2. Select datalength ('ssss ')

Example 2: (different, datalength is twice the value of Len ):

1. Select Len (n'ssss ')
2. Select datalength (n'ssss ')

Example 3: (different, datalength is more than twice that of Len, because Len does not contain trailing spaces ):

1. Select Len (n'ssss ')
2. Select datalength (n'ssss ')

Example 4: (different, datalength is twice the value of Len, because Len does not contain trailing spaces but header spaces)

1. Select Len (n'ssss ')
2. Select datalength (n'ssss ')

Note: When the variable is null, Len and datalength are both 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 for Value Management, which helps 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 (20) set @ value = 'abc' select datalength (@ value) Select Len (@ value) These statements return 3 values. Because the varchar type uses three single-byte characters to store three character values. However, if you use the nvarchar type to manage values of the same length, it takes up to twice the bytes:

Declare @ value nvarchar (20) set @ value = 'abc' select datalength (@ value) Select Len (@ value) datalength () function returns 6, because each character using the Unicode Character Set occupies 2 bytes. The return value of Len () is 3, because this function returns the number of characters, not the number of bytes. The following is an interesting test: How many bytes will it take to store an integer variable with a value of 2? If you want to store an integer variable of 2 billion, how many bytes will it take? Try:

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 always uses four bytes regardless of the value. In essence, the Len () function treats the integer value as the converted data. Therefore, in this example, it returns 1 and 10 respectively, that is, the number of digits of the value.

This article from the csdn blog, reproduced in: http://blog.csdn.net/Hello_World_wusu/archive/2009/10/14/4667452.aspx