How to differentiate this LEN() and DATALENGTH() ?

Posted by Devanand under Sql Server on 6/16/2010 | Views : 1538 | Status : [Member] | Replies : 2
hi all

kindly clarify my doubts

1. what is the purpose of LEN() and DATALENGTH() functions ?
2. how to differentiate with an example ?

thanks in advance
devanand




Responses

Posted by: Syedshakeer on: 6/16/2010 [Member] Starter

Up
0
Down
Hi,
B]Len():-  

Length function return the number of Characters of the specified string expression,excluding blank spaces.

select Len ('inthiyaaz ') as length

OutPut:-length
9


DataLenght():-  DataLength function return the number of Characters of the specified string expression,including blank spaces. 


select datalength ('inthiyaaz ') as length
OutPut:- length
11


Syed Shakeer Hussain

Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 6/17/2010 [Member] [MVP] Silver

Up
0
Down
Hi

Try the folloiwng two scenarios...

DECLARE @A VARCHAR(15)

SELECT @A = 'DotnetFunda '
PRINT LEN(@A)
PRINT DATALENGTH(@A)
Result:
11
12
DECLARE @A CHAR(15)

SELECT @A = 'DotnetFunda '
PRINT LEN(@A)
PRINT DATALENGTH(@A)
Result:
11
15


Explanation:
LEN() function returns only the actual length of the characters (just Count)
DATALENGTH() function return the actual BYTES taken of the string. So the VARCHAR type is not fixed, its just variable, So it takes only the actual characters. so the DATALENGTH() returns 12.

But, CHAR type is fixed, so the entire BYTES used, so the DATALENGTH() returns 15.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response