SQL Server Do we need to remove some charaters from SUBSTRING length if start is not from 0?

mbzjlibv  于 2023-11-16  发布在  其他
关注(0)|答案(1)|浏览(123)

What's the difference between below two statements. Both return the same output "is a sentence"

DECLARE @MyStr nvarchar(max) = 'This is a sentence'
SELECT SUBSTRING(@MyStr, 6, LEN(@MyStr)-5)

and

DECLARE @MyStr nvarchar(max) = 'This is a sentence'
SELECT SUBSTRING(@MyStr, 6, LEN(@MyStr))

I ran both and the latter doesn't add any spaces or anything.

5t7ly7z5

5t7ly7z51#

The third parameter to SUBSTRING is the length of substring to take. In the first example, the length covers exactly the remainder of the string. In the second example, the length exceeds the actual available length of the string, by 5 characters. Regarding what happens when the length is greater than the available length, we can turn to the documentation for SUBSTRING :
length
Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

In other words, if the length parameter plus the start passed is greater than what is available, then SUBSTRING just returns as much as is available.

相关问题