CHARINDEX()

  • Thanks for all the tips. This is my first attempt at a Question of the Day, so I will definitely take all your suggestions into account (including pointing to 2012 documentation instead of 2005).

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

  • Calibear (5/21/2013)


    Thanks for all the tips. This is my first attempt at a Question of the Day, so I will definitely take all your suggestions into account (including pointing to 2012 documentation instead of 2005).

    It's very good job for Day one.. Keep it up..

    --
    Dineshbabu
    Desire to learn new things..

  • Straight forward question - thanks

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • memory refreshed

    Neeraj Prasad Sharma
    Sql Server Tutorials

  • Thanx

    1+

    Neeraj Prasad Sharma
    Sql Server Tutorials

  • nice basic question....:-)

    Manik
    You cannot get to the top by sitting on your bottom.

  • Did it little fast and smiled thereafter. 🙂

  • Hugo Kornelis (5/21/2013)


    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.

    I agree with the previous poster that the available answers made the selection of the right one too easy.

    Thanks to Hugo for the extra info. I didn't know starting before 1 reduced the length of the substring.

Viewing 8 posts - 16 through 23 (of 23 total)

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