Len() vs. Datalength()

Introduction

Many gets confused with Len() and Datalength(). Here I have given a small example to describe what is the main difference between them.

Using the code

DECLARE @Call_len VARCHAR(15)
DECLARE @Call_Dlen NVARCHAR(15)

SET @Call_len = 'Find Length'
SET @Call_Dlen = 'Find Length'

SELECT 
LEN(@Call_len) AS DATA1_LEN,
DATALENGTH(@Call_len) AS DATA1_DLEN,
LEN(@Call_Dlen) AS DATA2_LEN,
DATALENGTH(@Call_Dlen) AS DATA2_DLEN

Result:

DATA1_LEN	DATA1_DLEN	DATA2_LEN	DATA2_DLEN
    11              11              11              22 

In the above example, two variable have declared in two different datatypes. Where as Len() will count only the length of strings in the column and Datalength() will count the length of string as per datatype.

In short, LEN() is used to return no. of char. in string and DataLength() is used for no. of bytes.