none
SUBSTRING START INDEX IS IT ZERO BASED RRS feed

  • Question

  • According to the documentation here:

    http://msdn.microsoft.com/en-us/library/ms187748.aspx

    start_expression : Is an integer or bigint expression that specifies where the returned characters start. If start_expression is less than 1, the returned expression will begin at the first character that is specifed in value_expression.

    I am pulling my hair out though, because when I execute:

    SELECT SUBSTRING('OK', 0, 1);
    

    I get back nothing. Shouldn't I get starting at the first character, give me 1 character, which is O.
    • Edited by JustinK101 Tuesday, February 16, 2010 11:32 PM
    Tuesday, February 16, 2010 11:29 PM

Answers

  • Yes, the function description clearly contradicts to what it says.

    value_expression

    Is a character, binary, text, ntext, or image expression.

    start_expression

    Is an integer or bigint expression that specifies where the returned characters start. If start_expression is less than 1, the returned expression will begin at the first character that is specifed in value_expression. In this case, the number of characters that are returned is the largest value of either the sum of start_expression and length_expression or 0. If start_expression is greater than the number of characters in the value expression, a zero-length expression is returned.

    length_expression

    Is a positive integer or bigint expression that specifies how many characters of the value_expression will be returned. If length_expression is negative, an error is generated and the statement is terminated. If the sum of start_expression and length_expression is greater than the number of characters in value_expression, the whole value expression beginning at start_expression is returned.

    http://msdn.microsoft.com/en-us/library/ms187748.aspx

    Looks like a BOL documentation error to me.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Tuesday, February 16, 2010 11:36 PM
    Moderator

  • Looks like a BOL documentation error to me.


    I opened a Documentation topic on the issue:

    http://social.msdn.microsoft.com/Forums/en/sqldocumentation/thread/1c39276d-5ec8-4117-9a0e-33b8879ae71d


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, August 17, 2010 6:13 AM
    Moderator

All replies

  • According to the documentation here:

    http://msdn.microsoft.com/en-us/library/ms187748.aspx

    start_expression : Is an integer or bigint expression that specifies where the returned characters start. If start_expression is less than 1, the returned expression will begin at the first character that is specifed in value_expression.

    I am pulling my hair out though, because when I execute:

    <em>SELECT SUBSTRING('OK', 0, 1);</em>
    
    
    
    

    I get back nothing. Shouldn't I get starting at the first character, give me 1 character, which is O.
    Take a look further down in that webpage you provided:  http://msdn.microsoft.com/en-us/library/ms187748.aspx

    There's a bug report (dating back to 2008) stating that it is incorrect... and he gives a correction.  I have to agree with the correction.

    --Brad (My Blog)
    Tuesday, February 16, 2010 11:35 PM
    Moderator
  • Yes, the function description clearly contradicts to what it says.

    value_expression

    Is a character, binary, text, ntext, or image expression.

    start_expression

    Is an integer or bigint expression that specifies where the returned characters start. If start_expression is less than 1, the returned expression will begin at the first character that is specifed in value_expression. In this case, the number of characters that are returned is the largest value of either the sum of start_expression and length_expression or 0. If start_expression is greater than the number of characters in the value expression, a zero-length expression is returned.

    length_expression

    Is a positive integer or bigint expression that specifies how many characters of the value_expression will be returned. If length_expression is negative, an error is generated and the statement is terminated. If the sum of start_expression and length_expression is greater than the number of characters in value_expression, the whole value expression beginning at start_expression is returned.

    http://msdn.microsoft.com/en-us/library/ms187748.aspx

    Looks like a BOL documentation error to me.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Tuesday, February 16, 2010 11:36 PM
    Moderator
  • I see the correction at the very bottom of this page, but why it was not already corrected?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    I have no idea... I'm not sure how well those are tracked by MSFT.

    I made a mistake saying it was posted back in 2008.  Steve Knox, the guy who posted the bug report, joined MSDN back in 2008... it looks like he posted the bug report in July of 2009.
    --Brad (My Blog)
    Tuesday, February 16, 2010 11:41 PM
    Moderator
  • Ok thanks guys. I was going absolutely crazy, because just about every other language uses 0 based indexs for the start position of substring. Kind of a bad decision there, but ohh well.
    Tuesday, February 16, 2010 11:46 PM

  • Looks like a BOL documentation error to me.


    I opened a Documentation topic on the issue:

    http://social.msdn.microsoft.com/Forums/en/sqldocumentation/thread/1c39276d-5ec8-4117-9a0e-33b8879ae71d


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, August 17, 2010 6:13 AM
    Moderator