SUBSTRING

Question

Post reply

SUBSTRING

Kevin

SSCarpal Tunnel

Points: 4962
More actions
July 10, 2009 at 8:56 am

#223437

Comments posted to this topic are about the item SUBSTRING

Viewing 14 posts - 1 through 13 (of 13 total)

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

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 1

Another article that describes this in more detail.

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

-Kevin Williams

Michael Poppers SSCrazy Points: 2149 More actions · Answer 2

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 SSCarpal Tunnel Points: 4962 More actions · Answer 3

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

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

sknox SSChampion Points: 12388 More actions · Answer 4

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.

SQLWinther SSCertifiable Points: 6294 More actions · Answer 5

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 🙂

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform

SQAPro Right there with Babe Points: 742 More actions · Answer 6

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 SSC Journeyman Points: 87 More actions · Answer 7

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 Right there with Babe Points: 742 More actions · Answer 8

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 SSC Rookie Points: 44 More actions · Answer 9

I have big doubt that anybody can predict (theoretically, without running) the results of the following requests: 🙂

[font="Courier New"]SELECT SUBSTRING('123456', -7998, 8000 )

SELECT SUBSTRING('123456', -7998, 8001 ) [/font]

dawoodalgharib SSC Journeyman Points: 87 More actions · Answer 10

I think that this sql is not a real state in any case with any developer or DBA..but if one should try it, I think it should return all the string...:-P

dawoodalgharib SSC Journeyman Points: 87 More actions · Answer 11

I think that this sql is not a real state in any case with any developer or DBA..but if one should try it, I think it should return all the string...:-P

dawoodalgharib SSC Journeyman Points: 87 More actions · Answer 12

I think this is not a real case that any developer or DBA should face...but if one try to run this sql I think it will make error : "Index and length must refer to a location within the string."

dawoodalgharib SSC Journeyman Points: 87 More actions · Answer 13

I think this is not a real case that any developer or DBA should face...but if one try to run this sql I think it will make error : "Index and length must refer to a location within the string."