Len() with different datatypes

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

    Prasad.N
    Hyderabad-India.

  • 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

  • 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

  • thank q Koen Verbeeck

    Prasad.N
    Hyderabad-India.

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

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

  • Koen Verbeeck (7/7/2013)


    Nice one to start the week, thanks.

    +1 🙂

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

  • dang - reverse on varchar got me.

  • 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

  • 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/

  • 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

  • thank q Hugo Kornelis for clear explanation for my post.

    Prasad.N
    Hyderabad-India.

  • 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!

  • 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).

  • 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