Post reply

CHARINDEX()

  • May 20, 2013 at 8:48 pm

    #293545

    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

  • May 20, 2013 at 10:39 pm

    #1616830

    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

  • May 20, 2013 at 10:52 pm

    #1616832

    Good straight and easy question.. Thanks..

  • May 20, 2013 at 11:20 pm

    #1616840

    Good and easy one. Keep it up !!!

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

  • May 21, 2013 at 12:06 am

    #1616843

    Easy one

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

  • May 21, 2013 at 1:23 am

    #1616855

    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.

  • May 21, 2013 at 1:55 am

    #1616862

    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

  • May 21, 2013 at 2:36 am

    #1616884

    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

  • May 21, 2013 at 2:42 am

    #1616889

    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/

  • May 21, 2013 at 5:02 am

    #1616929

    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

  • May 21, 2013 at 5:45 am

    #1616946

    Thomas Abraham (5/21/2013)

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

    +1

  • May 21, 2013 at 6:55 am

    #1616975

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

  • May 21, 2013 at 7:20 am

    #1616994

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

    Dave Morris :alien:

    "Measure twice, saw once"

  • May 21, 2013 at 9:06 am

    #1617074

    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.

  • May 21, 2013 at 10:10 am

    #1617097

    Thanks for a REALLY easy one, Bill!

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

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