SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SUBSTRING


SUBSTRING

Author
Message
kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3633 Visits: 1323
Comments posted to this topic are about the item SUBSTRING



kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3633 Visits: 1323
Another article that describes this in more detail.

http://support.microsoft.com/kb/310421

-Kevin Williams



Michael Poppers
Michael Poppers
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 Visits: 416
Explanation: The string returned by SUBSTRING is truncated if Start + Length -1 is greater than the length of the Expression string.

I think Mr. Williams meant to say "less than" rather than "greater than."
kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3633 Visits: 1323
Michael Poppers (7/10/2009)
Explanation: The string returned by SUBSTRING is truncated if Start + Length -1 is greater than the length of the Expression string.

I think Mr. Williams meant to say "less than" rather than "greater than."


Sad My first question and I botched the Explanation. Oh well.



sknox
sknox
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3892 Visits: 2920
Interestingly enough, the MSDN documentation linked to from the answer incorrectly states:

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

while the knowledgebase article correctly states:

"In cases where the starting position is less than one, the ANSI specified behavior is to return a number of characters equal to the starting position plus length minus 1 (emphasis mine), starting at character position one."

I've commented on the MSDN documentation; hopefully they will correct it soon.
hakan.winther
hakan.winther
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3285 Visits: 612
[quote]sknox (7/10/2009)
Interestingly enough, the MSDN documentation linked to from the answer incorrectly states:

"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."
quote]

Yes the MSDN documentation is incorrect but SQL engine is following the ANSI standard, thankfully, but it is confusing with incorrect documentation Smile

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
SQAPro
SQAPro
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 143
AH so the explanation currently given, given, while probably a valid fact (the return would be truncated in that instance) has nothing to do with the behavior we are seeing, which is caused instead by the ANSI standards having to do with using a starting position that is less than one.

Doing something like SELECT SUBSTRING('123456', 2, 8) would invoke the truncation based on Start + Length-1 is > (length of expression). Understandably in that case you are not going to get back 8 characters, even though you said that is what you want, because it's not there to return to you. (I presume the alternative to truncating would be something like padding out with spaces? who knows.)
dawoodalgharib
dawoodalgharib
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 6
I think that the definition of SUBSTRING statement contains: "If start_expression is less than 1, the returned expression will begin at the first character that is specifed in value_expression".
So I think this definition meant that:
SELECT * SUBSTRING('123456',0,3) should return 123.
SQAPro
SQAPro
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 143
dawoodalgharib (7/11/2009)
I think that the definition of SUBSTRING statement contains: "If start_expression is less than 1, the returned expression will begin at the first character that is specifed in value_expression".
So I think this definition meant that:
SELECT * SUBSTRING('123456',0,3) should return 123.


Except that (see KB article linked in one of the earliest posts above) there isn an ANSI standard that SQL is conforming to that says if the start position is <1 then return the specified length -1 So yes it's starting at position 1, but it's also adjusting the length.

so the docs for that defintion of substring need to be updated, to reflect that additional info (as also mentioned above) since clearly the function is behaving as described in the KB, not as one might expect from reading the docs alone.
vsumskoy-644542
vsumskoy-644542
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 12
I have big doubt that anybody can predict (theoretically, without running) the results of the following requests: :-)

SELECT SUBSTRING('123456', -7998, 8000 )
SELECT SUBSTRING('123456', -7998, 8001 )

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search