What is the difference between len() and DATALENGTH. I tried both in a SELECT, both are returning the same value. Can someone give me an example?

DATALENGTH returns the length of the string in bytes, including trailing spaces. LEN returns the length in characters, excluding trailing spaces. For example,


SELECT
LEN('string'),
LEN('string '),
DATALENGTH('string'),
DATALENGTH('string '),
LEN(N'string'),
LEN(N'string '),
DATALENGTH(N'string'),
DATALENGTH(N'string ')


will return 6, 6, 6, 9, 6, 6, 12, 18

sql 小發現

null + 欄位  =null 
null 和任何的值相加, 都會變成null 



  isnull(@year, '')  把null 值設成空白