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

In this blog, we are going to point out the catchy difference between DATALENGTH() and LEN() function in SQL Server. Many of us get confused between these two functions. And it is also one of the most asked interview questions.

Let’s find out the differences.

	LEN()	DATALENGTH()
Definition	The LEN() function returns the number of characters required/used to represent an expression.	The DATALENGTH() function returns the number of bytes required/used to represent an expression.
Expression	LEN( expression ) Example: select 'ImaginationHunt' as StringValue, LEN('ImaginationHunt') as StringLength Output: StringValue StringLength ImaginationHunt 15	DATALENGTH( expression ) Example: select 'ImaginationHunt' as StringValue, DATALENGTH('ImaginationHunt') as StringLength Output: StringValue StringLength ImaginationHunt 15
Input is NULL	The LEN() function return null, if the expression is null. Example: select NULL as StringValue, LEN(NULL) as StringLength Output: StringValue StringLength NULL NULL	The DATALENGTH() function return null, if the expression is null. Example: select NULL as StringValue, DATALENGTH(NULL) as StringLength Output: StringValue StringLength NULL NULL
When input data type is nvarchar	LEN( expression ) Example: select 'ImaginationHunt' as StringValue, LEN(N'ImaginationHunt') as StringLength Output: StringValue StringLength ImaginationHunt 15	DATALENGTH( expression ) Example: select 'ImaginationHunt' as StringValue, DATALENGTH(N'ImaginationHunt') as StringLength Output: StringValue StringLength ImaginationHunt 15
Trailing blank spaces	LEN() function doesn’t count the trailing blank spaces. Example: select 'ImaginationHunt     ' as StringValue, LEN('ImaginationHunt     ') as StringLength Output: StringValue StringLength ImaginationHunt 15	DATALENGTH() function count the trailing blank spaces. Example: select 'ImaginationHunt     ' as StringValue, DATALENGTH('ImaginationHunt     ') as StringLength Output: StringValue StringLength ImaginationHunt 20

For any query, comment us below.

Next - Most commonly used SQL Server Data Types #1

Previous – DATALENGTH() in SQL Server

Related Questions: -

#newtoprgm try the first program

Q-1 In this below mentioned SQL Server query, what will be the output?

select LEN(12345) as IntegerLength, LEN('') as StringLength, LEN(NULL) as StringLength1

A) 5, 0, 0

B) 4, 0, 0

C) 5, 0, NULL

D) 4, 0, NULL

Ans. Option (C).

Explanation: The output is divided into three parts: -

o   LEN(12345), expression 12345 contain 5 digit. So, length is 5.

o   LEN(‘’), expression ‘’ means empty string but NOT NULL. So, length is 0.

o   LEN(NULL), expression is NULL. So, length of NULL is NULL.

Q-2 In this below mentioned SQL Server query, what will be the output?

select DATALENGTH(12345) as IntegerLength, DATALENGTH('') as StringLength, DATALENGTH(NULL) as StringLength1

A) 5, 0, 0

B) 4, 0, 0

C) 5, 0, NULL

D) 4, 0, NULL

Ans. Option (D).

Explanation: The output is divided into three parts: -

Note: We know that DATALENGTH calculate the number of byte used by expression.

o   DATALENGTH(12345), expression 12345 contain 5 digit whose range will fall under ‘int’ data type. And ‘int’ data type takes storage space of 4 bytes. So, data length is 4.

o   DATALENGTH(‘’), expression ‘’ means empty string but NOT NULL. So, data length is 0.

o   DATALENGTH(NULL), expression is NULL. So, data length of NULL is NULL.

Click imagination hunt to read latest blogs.

Posted By - +Manish Kumar Gautam +LIVE VIAR +ASP.NET SOLUTIONS

Keep learning and sharing...