SUBSTR function

The SUBSTR function has the same purpose as the SUBSTRING function (to return a subset of a source string), but it uses different syntax.

SUBSTR Function


Element	Description	Restrictions	Syntax
length	Number of characters to be returned from source_string	Must be an expression, literal, column, or host variable that returns an integer	Expression
source_string	String that serves as input to the SUBSTR function	Must be an expression, literal, column, or host variable of a data type that can be converted to a character data type	Expression
start_position	Column position in source_string where the SUBSTR function starts to return characters	Must be an integer expression, literal, column, or host variable. Can have a plus sign ( + ), a minus sign ( - ), or no sign.	Literal Number

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

The SUBSTR function returns a subset of source_string. 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 Returned Subset
Positive	Counts forward from the first character in source_string
Zero (`0`)	Counts forward from the first character in source_string (that is, treats a start_position of 0 as equivalent to 1)
Negative	Counts backward from an origin that immediately follows the last character in source_string. A value of -1 returns the last character in source_string.

The length parameter specifies the number of logical characters (not the number of bytes) in the subset. If you omit the length parameter, the SUBSTR function returns the entire portion of source_string that begins at start_position.

If you specify a negative start_position whose absolute value is greater than the number of characters in source_string, or if length is greater than the number of characters from start_position to the end of source_string, SUBSTR returns NULL. (In this case, the behavior of SUBSTR is different from that of the SUBSTRING function, which returns all the characters from start_position to the last character of source_string, rather than returning NULL.)

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 following example specifies that the string of characters to be returned begins at a starting position 3 characters before the end of a 7-character source_string. This implies that the starting position is the fifth character of source_string. Because the user does not specify a value for length, the database server returns a string that includes all characters from character-position 5 to the end of source_string.

SELECT SUBSTR('ABCDEFG', -3)
   FROM mytable;

The following table shows the output of this SELECT statement.


(constant)
EFG