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 .

Sandhiya Priya · Answer

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)

Difference between Len() and DataLength()

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (10)