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.


Previous – DATALENGTH() in SQL Server

Related Questions: -


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...

Comments

Popular posts from this blog

SSC CGL 2016 Tier-1 Marks / Result

DATA TYPES IN C#

Pattern printing program

IS STRING MUTABLE OR IMMUTABLE IN .NET?

Top Mutual Funds