Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by kevin.l.williams » SUBSTRING
Add to briefcase
14 posts, Page 1 of 2
12»»

SUBSTRING Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
kevin.l.williams
Posted Friday, July 10, 2009 8:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:52 AM
Points: 2,796, Visits: 1,125
Comments posted to this topic are about the item SUBSTRING


Post #751142
kevin.l.williams
Posted Friday, July 10, 2009 9:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:52 AM
Points: 2,796, Visits: 1,125
Another article that describes this in more detail.

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

-Kevin Williams


Post #751148
Michael Poppers
Posted Friday, July 10, 2009 9:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 17, 2013 2:34 PM
Points: 565, Visits: 360
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."
Post #751158
kevin.l.williams
Posted Friday, July 10, 2009 9:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:52 AM
Points: 2,796, Visits: 1,125
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.


Post #751191
sknox
Posted Friday, July 10, 2009 11:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:31 AM
Points: 1,040, Visits: 1,356
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.
Post #751269
hakan.winther
Posted Friday, July 10, 2009 12:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:31 AM
Points: 2,226, Visits: 438
[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 :)

/Håkan Winther
MCITP:Database Developer 2008
Post #751316
SQAPro
Posted Friday, July 10, 2009 3:03 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 4:03 PM
Points: 164, 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.)
Post #751432
dawoodalgharib
Posted Saturday, July 11, 2009 11:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 28, 2009 10:37 PM
Points: 55, 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.
Post #751667
SQAPro
Posted Tuesday, July 14, 2009 4:23 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 4:03 PM
Points: 164, 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.
Post #753099
vsumskoy-644542
Posted Tuesday, July 28, 2009 9:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 04, 2009 6:54 AM
Points: 26, 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 )
Post #760886
« Prev Topic | Next Topic »

Add to briefcase
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.