difference between Len() and DataLength()

Question

difference between Len() and DataLength()

karthik M

One Orange Chip

Points: 29139
More actions
December 10, 2007 at 12:04 am

#377371

Hi Experts,
Len() - Will give the length of the character used,instead of length of the bit,excludes trailing spaces.
DataLength() - Will give the length of the bit used.
Apart from this, is there any difference ? i mean memory wise or storage wise or performance wise.
Experts inputs are welcome !
karthik

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

One difference is that NVARCHAR and VARCHAR store data differently. NVARCHAR uses two bytes per character vs. VARCHAR which stores it as one.

as in my example code ....

DECLARE @varChar VARCHAR(5)

DECLARE @nvarchar NVARCHAR(5)

SET @varChar = 'ABC'

SET @nvarchar = 'ABC'

SELECT

LEN(@varChar) AS varCharLength

,DATALENGTH(@varChar) AS varCharDataLength

,LEN(@NvarChar) AS varCharLength

,DATALENGTH(@NvarChar) AS varCharDataLength -- notice this length is 6 bytes

So the physical space reserved for NVARCHAR is double that of VARCHAR. This also applies to CHAR and NCHAR. Also, both VAR datatypes use an extra 2 bytes to store the length of the VAR data.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 2

DATALENGTH returns the length of the string in bytes, including trailing spaces. LEN returns the length in characters, excluding trailing spaces. For example,

SELECT

LEN('string'),

LEN('string '),

DATALENGTH('string'),

DATALENGTH('string '),

LEN(N'string'),

LEN(N'string '),

DATALENGTH(N'string'),

DATALENGTH(N'string ')

will return 6, 6, 6, 9, 6, 6, 12, 18

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Datalength works on text and ntext data types where len does not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Hi Vyas,

Can you tell me the difference between LEN('string') and Len(N'String') ?

Also for DataLength('string') and DataLength(N'string').

I am seeing this type of code first time.

Thanks in advance for your reply.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The N signifies that the following value is unicode (nvarchar/nchar)

The LEN should return the same, the datalenght for the N-prefixed value should be twice that for the value without the N.

See above difference between datalength on varchar and narchar for the reason.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 6

karthik M

One Orange Chip

Points: 29139

December 12, 2007 at 1:09 am

#759287

Thanks Gila and Vyas.:)

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 7

Gila and Vyas,

Suppose i need to find the length of a string.In that situation which function should i use ?

For example,

Declare @STR varchar(35)

Select @STR = 'Welcome to Sqlservercentral.com'

which statement should i use ?

select len(@str)

(or)

select DataLength(@str)

Which statement would provide good performance ?

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Depends what you want. I doubt there's much of a performance difference, if any.

Ifyou want the number of characters in the string, except trailing spaces, use LEN. If you what the storage size of the string, use Datalength

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

eng.aniltyagiji Valued Member Points: 57 More actions · Answer 9

SELECT

LEN('string'),

LEN('string '),

DATALENGTH('string'),

DATALENGTH('string '),

LEN(N'string'),

LEN(N'string '),

DATALENGTH(N'string'),

DATALENGTH(N'string ')

will give values 6,6,6,7,6,6,12,14

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Please note: 8 year old thread

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kellergary SSC Enthusiast Points: 119 More actions · Answer 11

kellergary

SSC Enthusiast

Points: 119

February 16, 2015 at 7:18 am

#1777820

Long, long ago

Programming[/url]