CHARINDEX()

  • Calibear

    Ten Centuries

    Points: 1377

    Comments posted to this topic are about the item CHARINDEX()

    --------
    For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
    You can also follow my twitter account to get daily updates: @BLantz2455

  • Lokesh Vij

    SSChampion

    Points: 10836

    Easy one for the day. Thanks Bill!!

    ~ 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

  • Jamsheer

    Ten Centuries

    Points: 1136

    Good straight and easy question.. Thanks..

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Good and easy one. Keep it up !!!

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

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    Easy one

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • Mighty

    SSCrazy Eights

    Points: 8815

    Hi, this was a nice question, however I don't fully agree with the wording

    For Smith, we don't substring it, since S is the first character

    SUBSTRING is still used, but since it starts at the first character of the string (start expression < 1), and the length expression is longer than the length of the string, the whole string is returned.

  • TomThomson

    SSC Guru

    Points: 104773

    Nice simple question.

    But why refer to a BOL page for an outdated version of SQL Server that is out of standard support, instead of to one of the current versions?

    Tom

  • palotaiarpad

    SSCertifiable

    Points: 5524

    It was easy to answer, but:

    - the right answer should be: it depends, as CHARINDEX uses the collation of the input.

    - the question is actually more about SUBSTRING as CHARINDEX

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Mighty (5/21/2013)


    Hi, this was a nice question, however I don't fully agree with the wording

    For Smith, we don't substring it, since S is the first character

    SUBSTRING is still used, but since it starts at the first character of the string (start expression < 1), and the length expression is longer than the length of the string, the whole string is returned.

    Yup, with one small caveat.

    As palotaiarpad says, the question is actually more about SUBSTRING than about CHARINDEX. For the Smith row (and assuming default collations - I am glad someone else pointed that out this time, as I start to grow tired of repeating myself), the CHARINDEX() function returns 1, subtract 1 and the result is 0. The effect of any start position below 1 is that SUBSTRING will return characters from the start of the string, but reduce the effective length by the difference between the start position and 1. So in this case, for Smith the effective SUBSTRING arguments are SUBSTRING ('Smith', 0, 10), which is equivalent to SUBSTRING('Smith', 1, 9). Since Smith has less than 9 characters, all of it is returned.

    Try the same code with 'Smithsonain', and you *will* note the difference between a start position of 0 or 1 with a length 10.

    Reference: http://msdn.microsoft.com/en-US/library/ms187748%28v=sql.110%29.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/

  • Thomas Abraham

    SSChampion

    Points: 10761

    I envision a day when people will cite HKOL instead of BOL.

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

  • Mighty

    SSCrazy Eights

    Points: 8815

    Thomas Abraham (5/21/2013)


    I envision a day when people will cite HKOL instead of BOL.

    +1

  • TaylorMade

    SSC Eights!

    Points: 966

    The topic is good, but it was easy only because the data and available answers were too simple. It would have been more challenging (what we all want I think?) if there were some names with multiple "s"s, names longer than 10, and names without an "S". Also should have been an answer with an error condition (I had initially thought there might be an issue with substring with a 0 start).

  • seatedElephant

    Say Hey Kid

    Points: 674

    Nice question thanks Bill, and thanks to Hugo for the extra information on SUBSTRING.

    Dave Morris :alien:

    "Measure twice, saw once"

  • rmacneill

    SSChasing Mays

    Points: 614

    This one got me - for this simple reason: asking myself - is "SubString" zero-based or one-based. I fell back to the zero-based mindset and as a result choose the incorrect answer. One day I will learn - or just stick with one progamming language.

  • Revenant

    SSC-Forever

    Points: 42467

    Thanks for a REALLY easy one, Bill!

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

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