Tag Archives: SQL LEN() Function

Difference between Len() and Datalength() functions in Sql Server

The LEN() and DATALENGTH() functions are there for completely different purpose. But it often confuses many in identifying which one to use when. The aim of this article is to clear all these confusions by presenting a comparative analysis of LEN() and DATALENGTH() Sql Serer functions.

[ALSO READ] Difference Between Sql Server VARCHAR and NVARCHAR Data Type

Below table lists out the comparative analysis between LEN() and DATALENGTH() Sql Server functions:

LEN() DATALENGTH()
DEFINITION
The LEN() Sql Server function returns the number of characters in the specified string expression The DATALENGTH() Sql Server function returns the number of bytes used/required to represent an expression
SYNTAX
LEN ( string_expression ) DATALENGTH ( expression )
INPUT PARAMETER
Input parameter is a string expression, it can be a constant or variable or column of character or binary data Input parameter is an expression of any data type
RETURN TYPE
bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise int
EXAMPLE
SELECT LEN('Shree') AS 'LEN'

RESULT:
LEN

SELECT DATALENGTH('Shree') 
 AS 'DATALENGTH'

RESULT:
DATALENGTH

EXCLUDES TRAILING BLANK SPACES?
YES. LEN() function excludes the trailing blank spaces while calculating the number of characters in the specified string expressionEXAMPLE:

SELECT LEN('Shree     ')  
 AS 'LEN'

RESULT:
Sql LEN function Trailing Spaces

NO. DATALENGTH() function includes the trailing blank spaces while calculating the number of bytes used/required to represent an expressionEXAMPLE:

SELECT DATALENGTH('Shree     ') 
 AS 'DATALENGTH'

RESULT:
Sql DATALENGTH function Trailing Spaces

INPUT PARAMETER IS NULL?
LEN(NULL) is NULL
Sql LEN of NULL
DATALENGTH(NULL) is NULL
Sql DATALENGTH of NULL
WHEN INPUT IS A UNICODE (i.e. double byte) CHARACTER STRING CONSTANT
For LEN() function it doesn’t matter whether input is a single byte or double byte (i.e. unicode) charcter strings, it always counts the number of characters.
EXAMPLE:

SELECT LEN(N'Shree') AS
 'LEN of Unicode chars'

RESULT:
Sql LEN of Unicode Character Constant expression

Note: The prefix N for any character string denotes that the following charcter string is of Unicode or double byte type

As we know each character in the UNICODE character string requires two bytes of storage. So DATALENGTH of UNICODE character string will be equal to the number characters including spaces multiplied by 2.
EXAMPLE:

SELECT DATALENGTH(N'Shree') AS
 'DATALENGTH of Unicode chars'

RESULT:
Sql DATALENGTH of Unicode Character Constant expression

WHEN INPUT IS A DOUBLE BYTE VARIABLE (EX: NVARCHAR DATATYPE)
DECLARE @Name NVARCHAR(50) 
 = 'Shree'
SELECT LEN(@Name) AS
 'LEN of Unicode chars'

RESULT:
Sql LEN of Unicode Character Variable

DECLARE @Name NVARCHAR(50)
 = 'Shree'
SELECT DATALENGTH(@Name) AS
 'DATALENGTH of Unicode chars'

RESULT:
Sql DATALENGTH of Unicode Character Variable

WHEN INPUT IS A DOUBLE BYTE VARIABLE (EX: NVARCHAR DATATYPE) HAVING VALUE WITH TRAILING SPACES
DECLARE @Name NVARCHAR(50) 
 = 'Shree     '
SELECT LEN(@Name) AS
 'LEN of Unicode chars'

RESULT:
Sql LEN of Unicode Character Variable trailing spaces

DECLARE @Name NVARCHAR(50)
 = 'Shree     '
SELECT DATALENGTH(@Name) AS
 'DATALENGTH of Unicode chars'

RESULT:
Sql DATALENGTH of Unicode Character Variable trailing spaces

SUPPORTS TEXT, NTEXT AND IMAGE DATA TYPES?
NO.

DECLARE @Customer TABLE
(TextColumn TEXT)

INSERT INTO @Customer
VALUES('100')

SELECT LEN(TextColumn) AS 'LEN'
FROM #t1

RESULT:
Msg 8116, Level 16, State 1, Line 7
Argument data type text is invalid for argument 1 of len function.

YES.

DECLARE @Customer TABLE
(TextColumn TEXT)

INSERT INTO @Customer
VALUES('100')

SELECT DATALENGTH(textcolumn)
  AS 'DATALENGTH'
FROM #t1

RESULT:
DATALENGTH
———–
3

ALSO READ