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

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

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