DATALENGTH() in SQL Server

In our previous blog, we have discussed the LEN() function in SQL Server.

In this blog, we are going to discuss the DATALENGTH() function in SQL Server.

DATALENGTH(): The DATALENGTH() function returns the number of bytes required/used to represent an expression.
 
DATALENGTH() in SQL Server

Syntax:

DATALENGTH( expression )

Things to Remember:

1. DATALENGTH() function includes the trailing blank spaces while calculating the number of bytes required/used to represent an expression.
2. The DATALENGTH() function return null, if the expression is null.
3. The DATALENGTH() function return twice the number of characters required/used to represent an expression when the data type is nvarchar. Since nvarchar store 2 byte per character.

Practical Implementation: -

The following example illustrate how to use the function.

I) With ‘varchar’ data type

(i) Finding the DATALENGTH of any expression.

declare @varString1 varchar(50)
set @varString1='LIVEVIAR'
select @varString1 as StringValue, DATALENGTH(@varString1) as StringLength

Output:

StringValue     StringLength
----------------    -----------------
LIVEVIAR       8.

(ii) Finding the DATALENGTH of any expression that contain blank spaces.

declare @varString2 varchar(50)
set @varString2='LIVEVIAR    '
select @varString2 as StringValue, DATALENGTH(@varString2) as StringLength

Output:

StringValue     StringLength
---------------     -----------------
LIVEVIAR       12

(iii) When expression is null.

declare @varString2 varchar(50)
set @varString2=NULL
select @varString2 as StringValue, DATALENGTH(@varString2) as StringLength

Output:

StringValue     StringLength
---------------     -----------------
NULL               NULL

II) With ‘nvarchar’ data type

(i) Finding the DATALENGTH of any expression.

declare @varString3 nvarchar(50)
set @varString3='LIVEVIAR'
select @varString3 as StringValue, DATALENGTH(@varString3) as StringLength

Output:

StringValue     StringLength
---------------     -----------------
LIVEVIAR       16

(ii) Finding the DATALENGTH of any expression that contain blank spaces.

declare @varString4 nvarchar(50)
set @varString4='LIVEVIAR    '
select @varString4 as StringValue, DATALENGTH(@varString4) as StringLength

Output:

StringValue     StringLength
---------------     -----------------
LIVEVIAR       24

(iii) When expression is null.

declare @varString4 nvarchar(50)
set @varString4=NULL
select @varString4 as StringValue, DATALENGTH(@varString4) as StringLength

Output:

StringValue     StringLength
---------------     -----------------
NULL               NULL

By this, we learn how and where to use DATALENGTH() in SQL Server.

For any query, comment us below.




For any query, comment us below.


Previous – LEN() in SQL Server

Related Questions: -


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

declare @strValue nvarchar(50);
set @strValue=N'IMAGINATIONHUNT';
select @strValue as Value,
DATALENGTH(@strValue) as StringStorageSize;

A) 30
B) 31
C) 15
D) 16
Ans. Option (A).
Explanation- The string ‘IMAGINATIONHUNT’ contain 15 character. And DATALENGTH() property say two byte a character. Therefore, 15 * 2 = 30.
The output for the following query will be:

Value                           StringStorageSize
---------------------------- ------------------------
IMAGINATIONHUNT 30

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

declare @strValue1 nvarchar(4);
set @strValue1=N'IMAGINATIONHUNT';
select @strValue1 as Value,
DATALENGTH(@strValue1) as StringStorageSize;

A) 30
B) 31
C) 15
D) 8
Ans. Option (A).
Explanation- As per the query we have to undergo certain keys:
(i) the variable ‘@strValue1’ is of nvarchar data type, which fall under Unicode Character Data Type category.
(ii) Length is 4. [because the value of n=4 in nvarchar(4)].
(iii) DATALENGTH() function return number of bytes used (2 * number of character) i.e., 2*4=8.
The output for the following query will be:

Value   StringStorageSize
--------- ------------------------
IMAG  8


Click imagination hunt to read latest blogs.


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

SSC CGL 2017 TIER 2 RESULT OUT

JUST IN TIME (JIT)

Can we have multiple Main methods in one class

TOP 15 VISUAL STUDIO SHORTCUT KEYS

Top 10 Interview Tips and Questions