Len() with different datatypes

  • Prasad.N

    Ten Centuries

    Points: 1083

    Comments posted to this topic are about the item Len() with different datatypes

    Prasad.N
    Hyderabad-India.

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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

    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

    thank q Koen Verbeeck

    Prasad.N
    Hyderabad-India.

  • demonfox

    SSCertifiable

    Points: 6289

    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

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

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    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

    dang - reverse on varchar got me.

  • Lokesh Vij

    SSChampion

    Points: 10836

    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: 64685

    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: 134309

    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[/url] but most of the time this is me

  • Prasad.N

    Ten Centuries

    Points: 1083

    thank q Hugo Kornelis for clear explanation for my post.

    Prasad.N
    Hyderabad-India.

  • RLilj33

    SSCrazy

    Points: 2153

    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: 2878

    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

    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!

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

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