Difference between Len() and DataLength()

Naresh Kumar

3y1.4kSQL

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sandhiya Priya8mo ago
Difference between LEN() and DATALENGTH() in SQL Server
Both LEN() and DATALENGTH() are used to measure length, but they serve different purposes.
1. LEN()
Returns the number of characters, excluding trailing spaces.
```
SELECT LEN('SQL Server   ') AS Result;
```
Output:
```
10
```
Counts characters
Ignores trailing spaces
Works with string data types
2. DATALENGTH()
Returns the number of bytes used to store the value, including trailing spaces.
```
SELECT DATALENGTH('SQL Server   ') AS Result;
```
Output:
```
13
```
Counts bytes
Includes trailing spaces
Works for strings, binary data, images, etc.
Key Differences
Feature LEN() DATALENGTH()
Counts Characters Bytes
Trailing spaces Ignored Included
Unicode support Characters only Shows actual storage
Useful for Display length Storage size
Unicode Example (NVARCHAR)
```
SELECT 
  LEN(N'Hello') AS LenResult,
  DATALENGTH(N'Hello') AS DataLengthResult;
```
Output:
```
LEN = 5
DATALENGTH = 10
```
?Because NVARCHAR uses 2 bytes per character
When to use which?
Use LEN() when you need the visible character count
Use DATALENGTH() when you need actual storage size or need to detect trailing spaces
Final Summary
- LEN() ? character count (no trailing spaces)
- DATALENGTH() ? byte count (includes trailing spaces)
0
Rajeev Kumar3y ago
LEN() Returns the number of characters of the specified string expression, excluding trailing blanks.DATALENGTH() to return the size in bytes for a given string.It show size in Bytes with space .

+1
Brahma Prakash Shukla3y ago
LEN() Returns the number of characters of the specified string expression, excluding trailing blanks. DATALENGTH() Returns the number of bytes used to represent any expression
+3
Satya Karki3y ago
LEN() Returns the number of characters of the specified string expression, excluding trailing blanks. DATALENGTH() Returns the number of bytes used to represent any expression.

https://database.guide/len-vs-datalength-in-sql-server/
+6
Rijwan Ansari3y ago
Use the LEN to return the number of characters encoded into a given string expression, and DATALENGTH to return the size in bytes for a given string expression. These outputs may differ depending on the data type and type of encoding used in the column.

Syntax for Len

LEN ( string_expression )

Syntax for Datalength

DATALENGTH ( expression )
+7

Feature	LEN()	DATALENGTH()
Counts	Characters	Bytes
Trailing spaces	Ignored	Included
Unicode support	Characters only	Shows actual storage
Useful for	Display length	Storage size

Vishal Yelve3y ago

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 DATALENGTH(NULL) is 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

Jignesh Kumar3y ago

Hi Naresh,


LEN : Return number of characters in variable. It will trim trailing space not leading space

DECLARE @Name VARCHAR(20)='c-sharpcorner'   
SELECT LEN(@Name) as [len] 
-- out put : 13
  
DECLARE @Name VARCHAR(20)='c-sharpcorner  '   
SELECT LEN(@Name) as [len]  
-- out put : 13
  
DECLARE @Name VARCHAR(20)='  c-sharpcorner'   
SELECT LEN(@Name) as [len]   
-- out put : 15

DATALENGTH : returns the number of bytes occupy in a variable. It also considered the spaces also.

DECLARE @Name1 VARCHAR(20)='c-sharpcorner '   
SELECT DATALENGTH(@Name1) as [DataLength] 
--Varchar out put : 14

DECLARE @Name1 NVARCHAR(20)='c-sharpcorner '   
SELECT DATALENGTH(@Name1) as [DataLength]   
--Nvarchar out put : 28

Mukesh Nailwal3y ago
Hello @Naresh Kumar,

Please refer https://www.c-sharpcorner.com/blogs/difference-between-len-and-datalength-function-in-sql1 to know the difference between Len() and DataLength()

Thanks
+7
Rajanikant Hawaldar3y ago
https://www.c-sharpcorner.com/blogs/difference-between-len-and-datalength-function-in-sql
+7
Amit Mohanty3y ago
LEN : The LEN function returns the number of characters in a variable. It also removes the trailing spaces and then return the length.
Example:
```
DECLARE @Name VARCHAR(20)='Amit' 
DECLARE @NameWithSpace VARCHAR(20)='Amit '     
SELECT LEN(@Name) as [Length], LEN(@NameWithSpace) AS [LenghtWithSpace]
```
Result: Both return 4.

DATALENGTH : This function returns the number of bytes occupy in a variable. It also considered the spaces also.
Example:
```
DECLARE @Name VARCHAR(20)='Amit' 
DECLARE @NameWithSpace VARCHAR(20)='Amit '     
SELECT DATALENGTH(@Name) as [DLength], DATALENGTH(@NameWithSpace) AS [DLenghtWithSpace]
```
Here DataLength of 1st variable is 4 but 2nd one is 5.
+6

Difference between Len() and DataLength()

10 Replies

Difference between `LEN()` and `DATALENGTH()` in SQL Server

1. `LEN()`

2. `DATALENGTH()`

Key Differences

Unicode Example (NVARCHAR)

When to use which?

Final Summary

Difference between Len() and DataLength()

Difference between LEN() and DATALENGTH() in SQL Server

1. LEN()

2. DATALENGTH()

Key Differences

Unicode Example (NVARCHAR)

When to use which?

Final Summary

Difference between `LEN()` and `DATALENGTH()` in SQL Server

1. `LEN()`

2. `DATALENGTH()`