String Length

Question

String Length

Viewing 15 posts - 16 through 30 (of 42 total)

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

Ken SSCoach Points: 17191 More actions · Answer 1

IowaDave (7/11/2012)
Why does datalength of nchar(40) give 80?!?? That really threw me.
Thanks!

Because it is the entire length of the string * 2 bytes. Nchar includes padding so the length is 40 versus 7 with nvarchar which does not include padding.

Dana Medley SSCertifiable Points: 6764 More actions · Answer 2

Thanks for the great question. I was actually brushing up on len() and datalength() just a couple of days ago so it was still really fresh on my brain. 😀

Everything is awesome!

DugyC Hall of Fame Points: 3804 More actions · Answer 3

Great question Kenneth, refreshed the old memory banks there 😉

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Divine Flame SSCoach Points: 15943 More actions · Answer 5

Divine Flame

SSCoach

Points: 15943

July 11, 2012 at 7:20 am

#1511526

Good question!

Sujeet Singh

Thomas Abraham SSChampion Points: 10761 More actions · Answer 6

Thanks Kenneth for a very good question. It was well written and explained. (Only improvement to code would be to alias computed columns.) And, with a short amount of code, several basic principles were highlighted. Well done!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 7

Thomas Abraham (7/11/2012)
Thanks Kenneth for a very good question. It was well written and explained. (Only improvement to code would be to alias computed columns.) And, with a short amount of code, several basic principles were highlighted. Well done!

I probably should have added alias' and usually would in code I write for someone else. In this case I wasn't really writing the code to be run so the alias' seemed a bit irrelevant.

Thanks everyone for the kind words. I was actually explaining the problem with using LEN on a CHAR to one of my coworkers when I decided it would make a good question. I've always liked the trick of adding a ; or some other character to the end of the string before using LEN. That and DATALENGTH is just a cool function.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

EL Jerry SSCertifiable Points: 7061 More actions · Answer 8

Thank you for the nice, easy, back-to-basics question.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 9

OzYbOi d(-_-)b

SSCarpal Tunnel

Points: 4054

July 11, 2012 at 9:40 am

#1511638

great question - cheers

sestell1 SSChampion Points: 10230 More actions · Answer 10

Nice question!

It really bugs me that len() trims whitespace padding on the right.

Revenant SSC-Forever Points: 42467 More actions · Answer 11

Revenant

SSC-Forever

Points: 42467

July 11, 2012 at 2:22 pm

#1511798

An interesting one -- thanks!

garmanthomas Grasshopper Points: 10 More actions · Answer 12

I guess if you are guessing, its hard. Sort of like

2+2=

A) 3

B) 4

C) 5

D) 7

It all depends on whether or not you know what "+" means, which we all know from reading our Wittgenstein is a rather thorny philosphical issue.

Miles Neale SSChampion Points: 13147 More actions · Answer 13

Excellent. I know what + means and still was challenged.

Not all gray hairs are Dinosaurs!

TomThomson SSC Guru Points: 104773 More actions · Answer 14

Hugo Kornelis (7/11/2012)
In my opinion, it is LEN() that has the strange behaviour here, because it returns the length of the padded version of the string instead of the length of the string itself. This almost caused me to give the wrong answer - but I recalled just in time the number of times I have already been bitten by this strange behaviour of LEN().

I don't understand that, Hugo. LEN isn't doing anything with padding except ignoring it. It's the concatenate operation that turns the padding from padding to not padding by putting non-space characters beyond the padding.

Tom

Paul White SSC Guru Points: 150467 More actions · Answer 15

L' Eomot Inversé (7/25/2012)
Hugo Kornelis (7/11/2012)
In my opinion, it is LEN() that has the strange behaviour here, because it returns the length of the padded version of the string instead of the length of the string itself. This almost caused me to give the wrong answer - but I recalled just in time the number of times I have already been bitten by this strange behaviour of LEN().
I don't understand that, Hugo. LEN isn't doing anything with padding except ignoring it. It's the concatenate operation that turns the padding from padding to not padding by putting non-space characters beyond the padding.

+1 I'm not sure what Hugo meant here either, but I agree with you Tom.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi