Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Thursday, 29 September 2016

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

Featured post

Think that makes you rich and richer

 Napolean said: “You can think and grow rich, but if you can be brought up like most people with work and you won't starve, this wil...