Tag Archives: SQL LEN()

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

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