String comparison

  • sestell1

    SSChampion

    Points: 10230

    Comments posted to this topic are about the item String comparison

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question ..... and it teaches something additional ... it teaches one to read definitions as well as the T-SQL very, very closely

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Koen Verbeeck

    SSC Guru

    Points: 258907

    Great question.

    Although, the first X returned is not just X but X with 19 spaces. SSMS doesn't really show it that well, but if you copy paste the value you'll see the spaces.

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

  • Nakul Vachhrajani

    SSChampion

    Points: 10151

    Very good question! The question proves that the devil is in the details! The question teaches everyone a lot of things.

    Thank-you!

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4867

    A very good question, even if i missed it. ๐Ÿ˜›

    I thought, if the LEN delivers 1, then the LIKE makes the same. For me a bit inconsistent, but good to know how stuff works.

  • Iulian -207023

    SSCertifiable

    Points: 7507

    Nice question, thank you

    Iulian

  • David McKinney

    SSChampion

    Points: 10358

    Excellent question. I often come across code such as

    IF col1 like col2 (without any wildcards)

    and I've often wondered why they didn't just put IF col1=col2. Til today I assumed they were equivalent. (I wonder if those that wrote this code appreciate the nuance.)

    Thanks for expanding my knowledge.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Nice question!

    However, the explanation and reference are slightly incomplete, as they don't explain the behaviour of @a LIKE @B versus @ LIKE @a.

    The explanation for that can simply be found in the Books Online article on LIKE, in the "Remarks" section. Link: http://msdn.microsoft.com/en-us/library/ms179859.aspx


    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/

  • ralm

    Hall of Fame

    Points: 3117

    Nice question.

    When we print the variables we can see the padded space in @a.

    PRINT @a

    PRINT @B

    PRINT 'A:'+@A + 'B:'+ @B+':'

    [font="Verdana"]Regards,
    Rals
    [/font].
  • Gobikannan

    SSCrazy

    Points: 2735

    Nice question.

    -----------------
    Gobikannan

  • TomThomson

    SSC Guru

    Points: 104762

    Good question.

    It would have been somewhat shocking if I had got this one wrong after writing the 15th April 2010 QotD ๐Ÿ˜›

    Tom

  • TomThomson

    SSC Guru

    Points: 104762

    Hugo Kornelis (5/9/2012)


    Nice question!

    However, the explanation and reference are slightly incomplete, as they don't explain the behaviour of @a LIKE @B versus @ LIKE @a.

    The explanation for that can simply be found in the Books Online article on LIKE, in the "Remarks" section. Link: http://msdn.microsoft.com/en-us/library/ms179859.aspx

    Actually I think he did explain it - I guess it depends on how you interpret "on the right". I interpret it as meaning "on the right hand side of "LIKE", I guess you must interpret it as "on the right hand side of a string" which is a bit bizarre because if that's what he'd have got it wrong and had two "false" values instead of getting it right and having one. The wording could have been better, but it seems perverse to interpret as implying a different answer from teh one he gave.

    Tom

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    L' Eomot Inversรฉ (5/9/2012)


    Hugo Kornelis (5/9/2012)


    Nice question!

    However, the explanation and reference are slightly incomplete, as they don't explain the behaviour of @a LIKE @B versus @ LIKE @a.

    The explanation for that can simply be found in the Books Online article on LIKE, in the "Remarks" section. Link: http://msdn.microsoft.com/en-us/library/ms179859.aspx

    Actually I think he did explain it - I guess it depends on how you interpret "on the right". I interpret it as meaning "on the right hand side of "LIKE", I guess you must interpret it as "on the right hand side of a string" which is a bit bizarre because if that's what he'd have got it wrong and had two "false" values instead of getting it right and having one. The wording could have been better, but it seems perverse to interpret as implying a different answer from teh one he gave.

    You are right, Tom. On the right can only be meant to apply to he LIKE and not to the string, as that is already implied by the word "trailing". I didn't read the explanation carrefully enough.


    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/

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    Nice question!!!!

    thanks!!!!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • sjimmo

    SSChampion

    Points: 11139

    Nice question - almost missed it. Re-Review of code caused me to change my answer at the last minute. ๐Ÿ™‚

    Steve Jimmo
    Sr DBA
    โ€œIf we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

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

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