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.

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

Previous – LEN() in SQL Server

Related Questions: -

#newtoprgm try the first program

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.

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

Keep learning and sharing...

Read More »