A pit in sql server-the difference between len and datalength

When dealing with problems today, when counting the maximum number of bytes in a field, there was a problem:

select max(len(subject_name)) from dbtabletest;

But the return value is 129.

However, there is always an error on the oracle side, saying that the number of inserted characters is too large, which is really strange.

After a while, I copied the subject_name and found too many spaces behind a line of values in the text editor. Until now, I didn't know that I needed to use datalength to count the spaces at the end. I was really cheated by sql server again.

Fortunately, he finally found the problem!

When using non-Unicode encoding, i.e. varchar type string, DataLength() and Len() are different:

1. Space processing

Len() The number of characters in a string expression, excluding trailing spaces, but counting leading and middle spaces;

DataLength() The number of bytes of any expression, including spaces.

2. Processing of Chinese characters

The difference is that Len only returns the number of characters, and a Chinese character represents a character. Datalength returns the number of bytes, two bytes for a Chinese character.