Difference between Len() and DataLength()

Question

Rijwan Ansari · Answer

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 )

Satya Karki · Answer

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/

Jignesh Kumar · Answer

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 Nailwal · Answer

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

Rajanikant Hawaldar · Answer

https://www.c-sharpcorner.com/blogs/difference-between-len-and-datalength-function-in-sql

Amit Mohanty · Answer

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.

Vishal Yelve · Answer

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: SELECT DATALENGTH( 'Shree' ) AS 'DATALENGTH' RESULT: EXCLUDES TRAILING BLANK SPACES? YES. LEN() function excludes the trailing blank spaces while calculating the number of characters in the specified string expression EXAMPLE: SELECT LEN( 'Shree ' ) AS 'LEN' RESULT: NO. DATALENGTH() function includes the trailing blank spaces while calculating the number of bytes used/required to represent an expression EXAMPLE: SELECT DATALENGTH( 'Shree ' ) AS 'DATALENGTH' RESULT: 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: 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: WHEN INPUT IS A DOUBLE BYTE VARIABLE (EX: NVARCHAR DATATYPE) DECLARE @ Name NVARCHAR(50) = 'Shree' SELECT LEN(@ Name ) AS 'LEN of Unicode chars' RESULT: DECLARE @ Name NVARCHAR(50) = 'Shree' SELECT DATALENGTH(@ Name ) AS 'DATALENGTH of Unicode chars' RESULT: 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: DECLARE @ Name NVARCHAR(50) = 'Shree ' SELECT DATALENGTH(@ Name ) AS 'DATALENGTH of Unicode chars' RESULT: 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

Brahma Prakash Shukla · Answer

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

Rajeev Kumar · Answer

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 .

Difference between Len() and DataLength()

Answers (9)