Differences between Datalength () and Len () two built-in functions in SQL Server (reproduced)

Recent work encountered a problem: when declaring a field type in the database char (4), but actually only stored the ' DCE ' three letters, the program is assembled in the field as a key map, will be ' DCE ' + space as its key, so that the use of no space in the ' DCE ' key to fetch value is not taken out, the result is null. Later, the database field type was viewed to find the problem. As we all know, the difference between char and varchar is that one is fixed length and one is variable length. In the process of looking for a problem, we use the two built-in functions we are going to say today, datalength () and Len ().
Before explaining the difference between datalength () and Len (), let's take a look at the difference between varchar (n) and nvarchar (n).

• VARCHAR (n): Character data with a length of n bytes and non-Unicode encoded characters, n must be a numeric value between 1 and 8000. Storage size is the actual length of bytes of input data, not n bytes.
• nvarchar (n): variable-length Unicode character data with n characters, n must be between 1 and 4000, and the storage size of bytes is twice times the number of characters entered.

That is, varchar (2) can store up to 2 letters, or 1 kanji, while nvarchar (2) can store up to 2 letters, or two characters, meaning nvarchar (2) contains two characters = 4 bytes.
Having learned the above, let's look at the difference between datalength () and Len () by Example:

-- declaring scalar variables   Declare @a varchar (Max)  

-- Assigning a value to a variable ' AAA ' Set @a = ' AAA '

-- Query length separately Select LEN (@a as A_len,datalength (@a as A_datalength

1, @a= ' AAA ', the results are as follows:

2, @a= ' AAA ', here the tail added two spaces, the result is as follows:

3, @a= ' AAA ', where the front added two spaces, the results are as follows:

4, @a= ' A A ', where a between each plus a space, the result is as follows:

The following conclusions are drawn:

The difference between datalength () and Len () when using non-Unicode encoding, which is a varchar-type string:

• Len () The number of characters in the string expression, excluding trailing spaces, but counting the head space and the middle space;
• DATALENGTH () The number of bytes of any expression, including spaces, so when the data type is nvarchar, actually datalength () divided by 2 equals Len () calculates the number of characters after a space at the end of the string, so the SQL The best way to nvarchar string lengths in Server is datalength ()/2.

When using Unicode encoding, interested students can try what results themselves.

Original link

Differences between Datalength () and Len () two built-in functions in SQL Server (reproduced)