Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SUBSTRING Expand / Collapse
Author
Message
Posted Friday, July 10, 2009 8:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:30 AM
Points: 3,142, Visits: 1,261
Comments posted to this topic are about the item SUBSTRING


Post #751142
Posted Friday, July 10, 2009 9:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:30 AM
Points: 3,142, Visits: 1,261
Another article that describes this in more detail.

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

-Kevin Williams



Post #751148
Posted Friday, July 10, 2009 9:10 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 654, Visits: 400
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
Posted Friday, July 10, 2009 9:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:30 AM
Points: 3,142, Visits: 1,261
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
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: Friday, September 12, 2014 7:18 PM
Points: 1,325, Visits: 1,693
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
Posted Friday, July 10, 2009 12:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 3:16 PM
Points: 2,548, Visits: 542
[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
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Post #751316
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
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
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
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 4, 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 12»»

Permissions Expand / Collapse