String Handling Functions Part 6

In this lesson we'll look at how to use the LEN and DATALENGTH functions. Most of use LEN and it normally works well, but we'll show you one case where you might not get the results you expect! The same is true for DATALENGTH, it can look like the perfect solution (and it might be), but it also has a couple behaviors you have to understand to get to consistently right answers.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Gil on 10/25/2010

I think it would have been wise to show that if you use DATALENGTH with an nvarchar, you need to divide by 2 to convert the number of bytes to characters.

Ed Svastits on 10/25/2010

Good explanation between LEN() and DATALEN(). A useful function to get rid of leading spaces is LTRIM() if you just want to determine the length of non-space characters in a string.

Kenneth Wymore on 10/25/2010

Good info. I never used datalength because of the way it handles char. Thank you for showing how to use it and when.

Orlando on 10/25/2010

Nice spin through datalength.

Mark Hions on 10/25/2010

You could make the output easier to see

Dexter Jones on 10/25/2010

Clear and concise, as usual. Thanks, Andy!

kishore on 10/25/2010

very nice

Terrance on 10/25/2010

Very good video in explaining the difference between len and datalength.

Diana Dee on 10/25/2010

_Please_ increase the font size of the results. Those 0, 2, and 3 numbers were almost unreadable. Thanks.

Leonard Peoples on 10/25/2010

Very helpful. I like the ugly hack. :-)

Carla Wilson on 10/25/2010

Demonstrating datalength with nvarchar would have been another good "twist" to point out.

Joe on 10/25/2010

Good reminder about trimming trailing spaces.

SQLSharma on 10/25/2010

sweet.

kalapala baburao on 10/26/2010

good

Noel on 10/26/2010

It would be great if there was a link on the videos to download/open the code shown

shakur on 10/26/2010

Good lesson

Jack Pines on 10/26/2010

You've graciously upped the font size on the query window. Thank you. Would you do likewise on the Results/Output window, please?

James Lawrence on 10/26/2010

some good tips here...

WChaster on 10/26/2010

So much I have to learn.

B730228344 on 10/26/2010

Good explanation from Andy as usual

valluru raghu on 10/26/2010

good

Kory Skistad on 10/27/2010

It may be worth mentioning that the behavior for trailing spaces that Andy demonstrated is controlled by the ANSI_PADDING setting. The default setting is ON which preserves trailing spaces. Setting this to OFF will ignore trailing spaces. However, Microsoft recommends you leave the default setting. In fact, it claims that in future versions of SQL Server, the only option will be ON and setting to OFF will produce an error.

Don Weigend on 10/27/2010

I did not know that the length function does a right trim, that is useful to know. Thanks for the great tip!

Alexander on 10/29/2010

good video

ad on 10/30/2010

very good

Sam Schafer on 11/1/2010

Excellent demo of the differences between the two.

swetha on 12/17/2010

Helpful. Thanks

Derek on 2/18/2011

Wow, I never knew that LEN didn't take into account trailing spaces. Good info!

Mark Horninger on 8/11/2011

How does row compression affect datalength.