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)ornvarchar(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)?