4

In SQL Server, given a string, is there an established, canonical "best" way to get a substring starting at a particular index, and continuing to the end of the string?

"Best" here means a balance of the following considerations:

  • Most efficient performance-wise
  • Easiest for developers to read and understand
  • Shortest / easiest to remember without having to look up the syntax

Ways I've come across to do this, using 2 in these examples as the index from which to start the substring:

SUBSTRING(mystring, 2, 1000000)
  • Source: https://stackoverflow.com/a/8302533/12484
  • Con: The use of the "magic number" 1000000 (which could be any "sufficiently large" value) seems not ideal.
  • Con: What my code encounters a string that is longer than that value?

.

SUBSTRING(mystring, 2, 2147483647)
  • Con: There's no built-in constant that we can substitute for that "max int" value.
  • Con: That "max int" value is hard to remember, and is annoying to have to Google every time.
  • Con: Even assuming we're talking about an varchar(max) or nvarchar(max), the string length might possibly exceed that size, depending on the SQL Server version?

.

SUBSTRING(mystring, 2, LEN(mystring) - 2 + 1)
  • Con: This syntax is somewhat cumbersome.

.

RIGHT(mystring, LEN(mystring) - 2 + 1)
  • Con: This is still is fairly cumbersome.

.

SUBSTRING(mystring, 2, LEN(mystring))
  • Con: Per the docs, it's okay for the "length" parameter to have a value that goes past the end of the string, but this might initially seem like a possible index-out-of-range bug to developers reading this code (until they also look up the docs and see that this approach is ok)?

Is there a better general solution to this problem, taking into account the considerations that I mentioned above (performance, readability, ease of use)?

Jon Schneider
  • 25,758
  • 23
  • 142
  • 170
  • what mean canonical here? In Spanish is related to catholic religion – Juan Carlos Oropeza Sep 15 '17 at 15:15
  • @JuanCarlosOropeza I'm using it to mean "an accepted principle or rule", see definition 4 here: https://www.merriam-webster.com/dictionary/canon – Jon Schneider Sep 15 '17 at 15:17
  • 2
    As far as I know, there is no canonical way, all the above options are perfectly valid, choose the one you feel most comfortable with. – DavidG Sep 15 '17 at 15:20
  • Not sure what's the 'best' way, you could also use STUFF(mystring, 1,2,'') – EricZ Sep 15 '17 at 15:24
  • SUBSTRING(mystring, 2, LEN(mystring)) is the simplest and clearest IMHO. All of the other ones you have thing about or do some math (as does the SQL engine) – S3S Sep 15 '17 at 15:32
  • 1
    I'm tempted to close this one because seem to be very opinion based. All produce the same result, and have same performance. And what you said is cumbersome or confusing I just think is normal. – Juan Carlos Oropeza Sep 15 '17 at 15:33
  • @JuanCarlosOropeza If asking for the "best" way is resulting in opinion-based answers (even though I tried to define objective criteria for what constitutes a "best" solution), we could just rework this question to a more simple "In SQL Server how do I get a substring starting at an index and continuing to the end of the string", and let the voters decide among answers which is the "best" one, as normal? (I searched before asking, and couldn't find a duplicate of this question, even though it seems fairly basic.) – Jon Schneider Sep 15 '17 at 15:39
  • 1
    The question is well formatted and interesting, but I feel you're just going to get the same answers that are posted in the duplicate I suggested, hence my vote. – Tanner Sep 15 '17 at 15:44
  • @Tanner I saw the same question, but If I mark it as duplicated will be closed. I think is opinion based because there is no way to say which one is better. Is just a matter of taste and which one you like more. – Juan Carlos Oropeza Sep 15 '17 at 15:48
  • @JuanCarlosOropeza i assume you have the big hammer :-) wield it carefully. I just feel there won't be any new or better answers than the ones already posted on other similar questions. – Tanner Sep 15 '17 at 15:49
  • 1
    @Tanner I just realize, if mark it as opinion based my vote count but doesn't get closed. Sorry Jon, I just don't think you will get any different answer. – Juan Carlos Oropeza Sep 15 '17 at 15:59

2 Answers2

2

As I mentioned in the comment, another option you could use is 

STUFF(mystring, 1,2,'')

This should be easy to understand and remember for developers.

EricZ
  • 6,065
  • 1
  • 30
  • 30
1
SUBSTRING(mystring, 2, LEN(mystring) - 2 + 1)

This would definitely be my preferred way to do it. Even if "canonically" you can extend beyond the bounds of the string's length in a SUBSTRING() function, its better not to. If you are concerned about confusions with the syntax, simply preface the line with a comment explaining. In fact, I would probably preface a this kind of operation with a comment anyway to explain why I am using a substring and what I anticipate the substring to consist of

Ryan Smith
  • 41
  • 2