Len() with different datatypes

Question

Len() with different datatypes

Prasad.N

Ten Centuries

Points: 1083
More actions
July 6, 2013 at 12:28 pm

#279637

Comments posted to this topic are about the item Len() with different datatypes
Prasad.N
Hyderabad-India.

Viewing 15 posts - 1 through 15 (of 26 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Nice one to start the week, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Nice easy question, although at first site it looks rather unpleasant.

There are too many questions which would be easy if it was easy to see how many blanks there are. Questions that expect us to be able to count blanks (difficult when looking at HTML, so go look at the hex coding of the html on the QotD page or copy the code into notepad or something else that lets one count spaces easily). This question isn't like that, because only one of the answer options has 12 as the last number, and as that is obviously required it immediately delivers the answer (and then we can see from the rest of the answer that the other string had two trailing spaces - no need for any counting). so this is a good question that looks a bit like quite a few bad ones.

Tom

Prasad.N Ten Centuries Points: 1083 More actions · Answer 3

Prasad.N

Ten Centuries

Points: 1083

July 7, 2013 at 11:11 pm

#1630308

thank q Koen Verbeeck

Prasad.N
Hyderabad-India.

demonfox SSCertifiable Points: 6289 More actions · Answer 4

L' Eomot Inversé (7/7/2013)
Nice easy question, although at first site it looks rather unpleasant.
There are too many questions which would be easy if it was easy to see how many blanks there are. Questions that expect us to be able to count blanks (difficult when looking at HTML, so go look at the hex coding of the html on the QotD page or copy the code into notepad or something else that lets one count spaces easily). This question isn't like that, because only one of the answer options has 12 as the last number, and as that is obviously required it immediately delivers the answer (and then we can see from the rest of the answer that the other string had two trailing spaces - no need for any counting). so this is a good question that looks a bit like quite a few bad ones.

+1

Taking into account property of Char datatype, no need to look at the other options.

Thanks for the question.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 5

Started this week with nice question, looking for many more question like this.

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 6

Koen Verbeeck (7/7/2013)
Nice one to start the week, thanks.

+1 🙂

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

alkmini.sussa 57646 Newbie Points: 5 More actions · Answer 7

alkmini.sussa 57646

Newbie

Points: 5

July 8, 2013 at 12:54 am

#1630321

dang - reverse on varchar got me.

Lokesh Vij SSChampion Points: 10836 More actions · Answer 8

Nice and easy. thanks Prasad

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

Not a bad question, but the explanation is only partial. Here's a fuller explanation.

For varchar, trailing spaces are preserved, so the variable stores six letters plus two trailing spaces. For LEN, trailing spaces are ignored (as indicated in the explanation), so 6 is returned. But REVERSE operates on the entire string, yielding two leading spaces plus six letters. Leading spaces are not ignored for LEN - result = 8.

For char, values are padded with spaces to the specified length, so the variable stores six letters plus six trailing spaces. For LEN, trailing spaces are ignored (as indicated in the explanation), so 6 is returned. But REVERSE operates on the entire string, yielding six leading spaces plus six letters. Leading spaces are not ignored for LEN - result = 12.

Unlike Tom, I am not really happy that there was only one answer with the correct option for LEN(REVERSE(@Char)) - made it too easy. His point about counting spaces would have been valid if there had been answer options like 7, 8, and 9 - but that is not the case. From the fact that the only numbers in the answer options were 6 (no trailing spaces), 12 (max length) and 8, it was clear that there were two trailing spaces in the string constants.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 10

actually ... the correct answer may not be that correct at all :hehe:

It all depends on your ANSI_PADDING setting in use.

set ansi_padding off ;

declare @Vchar varchar(12)='prasad '

, @Char char(12)='prasad '

select len(@Vchar)

, len(@Char)

, len(reverse(@Vchar))

, len(reverse(@Char))

Result:

----------- ----------- ----------- -----------

6 6 8 6

(1 row(s) affected)

But, as stated in BOL for SQL2012:

In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

So the questions correct answer can still be accepted as valid after all.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Prasad.N Ten Centuries Points: 1083 More actions · Answer 11

thank q Hugo Kornelis for clear explanation for my post.

Prasad.N
Hyderabad-India.

RLilj33 SSCrazy Points: 2153 More actions · Answer 12

Thanks, Hugo.

As an regular user of SQL but not a full-time dba, I find these questions helpful in learning the nuances of the t-SQL language. My original thought was that the answer should be 6-12-8-12, so when it was 6-6-8-12 I was a little bewildered. After reading the explanation provided, I felt it was lacking.

Your explanation here was what I 'assumed' to be happening. Thanks for the clarification!

logitestus SSCrazy Points: 2928 More actions · Answer 13

This is my 2 cents, but I searched through BOL (my usual default starting location for answering TSQL questions) on both LEN and REVERSE and neither helped in the "correct" answer department. I also dug into the data types area and I could find no information as to why a LEN reverse on a char field would yield the length of the entire field.

Am I missing some important research location? The only reason why I got a "correct" answer is that I ran the query, my incorrect answer would have been 6,6,8,8 and that is based off the documentation (unless I am reading it incorrectly).

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

RLilj33 (7/8/2013)
Thanks, Hugo.
As an regular user of SQL but not a full-time dba, I find these questions helpful in learning the nuances of the t-SQL language. My original thought was that the answer should be 6-12-8-12, so when it was 6-6-8-12 I was a little bewildered. After reading the explanation provided, I felt it was lacking.
Your explanation here was what I 'assumed' to be happening. Thanks for the clarification!

The same here, but I got to the QotD late today.

Thanks, Prasad and Hugo!