SQL SUBSTRING functions

SUBSTRING (expression, start, length)

Parameters
expression
A string, binary string, text, image, column, or an expression that contains a column. Do not use an expression that contains an aggregate function.

Start
An integer or an expression that can be implicitly converted to int, specifying the starting position of the substring, starting at 1.

length
An integer or an expression that can be implicitly converted to an int, specifying the length of the substring. After testing, the discovery can only be non-negative .

return value

1. If expression is a supported binary data type, then binary data is returned, which we will not discuss for the moment.
2. If expression is a supported character data type, the character data is returned.

(1) If the index of start starts at 1, the string intercept starts at the first character of the expression, starting with the second character of the expression from 2, and so on.

For example:

Select SUBSTRING (' ABCDE ', up) returns the result AB

Select SUBSTRING (' ABCDE ', 2, 3) returns the result BCD

Select SUBSTRING (' ABCDE ', 1,0) returns the null result

Select SUBSTRING (' ABCDE ', 0,8) returns the result as ABCDE, noting that there are no spaces behind it.

(2) if the index of start starts at 1 (0 or negative ), the return length is equal to 1, the intercept length is the absolute value (start-1), and if the difference is negative, the return is empty .

Draw a diagram to understand this situation (e.g. substring (' ABCDE ', 0,2) returns a):

Coordinates: 0 1 2 3 4 5
Content: null a b c d E
Intercept: intercept from here to end

For example: the following | | Represents an absolute value calculation

Select SUBSTRING (' ABCDE ', 0,2) returns the result as a, the formula is SUBSTRING (1,2-|2-1|)

Select SUBSTRING (' ABCDE ', 0,-1) returns an error "invalid length parameter passed to SUBSTRING function"

Select SUBSTRING (' ABCDE ', -1,2) returns a null result with a formula of SUBSTRING (1,2-|-1-1|)

Select SUBSTRING (' ABCDE ', -5,3) returns a null result with a formula of SUBSTRING (1,3-|-5-1|)

Select SUBSTRING (' ABCDE ', -1,4) returns the result of AB, calculated as SUBSTRING (1,4-|-1-1|)

Select SUBSTRING (' ABCDE ', -2,8) returns the result as ABCDE, calculated as SUBSTRING (1,8-|-2-1|)

SQL SUBSTRING functions