SUBSTRING function

The SUBSTRING function returns a subset of a character string.

SUBSTRING Function

SUBSTRING(source_string FROMstart_position [ FORlength ] )
Element Description Restrictions Syntax
length Number of characters to return from source_string Must be an expression, constant, column, or host variable that returns an integer Literal Number
source_string String argument to the SUBSTRING function Must be an expression, constant, column, or host variable whose value can be converted to a character data type Expression
start_position Position in source_string of first returned character Must be an expression, constant, column, or host variable that returns an integer Literal Number

Any argument to the SUBSTRING function must be of a built-in data type.

The return data type is that of the source_string argument. If a host variable is the source, the return value is either NVARCHAR or NCHAR, according to the length of the returned string, using the return type promotion rules that the section Return Types from the CONCAT Function describes.

The subset begins at the column position that start_position specifies. The following table shows how the database server determines the starting position of the returned subset based on the input value of the start_position.
Value of Start_Position How the Database Server Determines the Starting Position of the Return Subset
Positive

Counts forward from the first character in source_string

For example, if start_position = 1, the first character in the source_string is the first character in the returned subset.
Zero (0)

Counts from one position before (that is, to the left of) the first character in source_string

For example, if start_position = 0 and length = 1, the database server returns NULL, whereas if length = 2, the database server returns the first character in source_string.
Negative

Counts backward from one position after (that is, to the right of) the last character in source_string

For example, if start_position = -1, the starting position of the returned subset is the last character in source_string.
The size of the subset is specified by length. The length parameter refers to the number of logical characters, rather than to the number of bytes. If you omit the length parameter, or if you specify a length that is greater than the number of characters from start_position to the end of source_string, the SUBSTRING function returns the entire portion of source_ string that begins at start_position. The following example specifies that the subset of the source string that begins in column position 3 and is two characters long should be returned: 
SELECT SUBSTRING('ABCDEFG' FROM 3 FOR 2) FROM mytable;
The following table shows the output of this SELECT statement.
(constant)
CD
In the following example, the user specifies a negative start_position for the return subset: 
SELECT SUBSTRING('ABCDEFG' FROM -3 FOR 7)
   FROM mytable;
The database server starts at the -3 position (four positions before the first character) and counts forward for 7 characters. The following table shows the output of this SELECT statement.
(constant)
ABC