Function group: string function
SUBSTRING() extracts a substring from a string.
SUBSTRING ( expression FROM startposition [FOR substring_length ][USING CODE_UNITS])
expression
Alphanumeric expression or national expression. Its evaluation must return either an alphanumeric string (data type CHAR or VARCHAR) or a national string (data type NCHAR or NVARCHAR). See also section "Compatibility between data types".
startposition
Numeric expression whose data type is DECIMAL or NUMERIC without decimal places (SCALE 0), SMALLINT or INTEGER. The evaluation of startposition returns an integer or a fixed-point number without decimal places. It cannot be a multiple value with a dimension greater than 1.
startposition specifies the position of a character in or outside the string returned when expression is evaluated. startposition specifies the character as of which the substring is to be extracted.
substring_length
Numeric expression whose data type is DECIMAL or NUMERIC without decimal places (SCALE 0), SMALLINT or INTEGER. The evaluation of substring_length returns an integer or a fixed-point number without decimal places. The value of substring_length cannot be less than 0. It cannot be a multiple value with a dimension greater than 1.
substring_length specifies the maximum length of the substring.
Result
In the following description of the possible results, string is the string returned when expression is evaluated.
The result is the NULL value when expression, startposition and/or substring have the NULL value.
The result is a string with a length of 0 when any of the following conditions are fulfilled:
startposition is greater than the number of characters in string.
string has the length 0.
substring_length is 0.
The sum of startposition and substring_length is <= 1.
Otherwise:
The result is a substring of string. The order in which the characters occur corresponds to the order of the characters in string. The substring contains the number of characters specified by startposition and substring_length:
substring_length is specified and startposition >=1:
The substring contains substring_length characters (but not beyond the last character of string), beginning with the character of string specified by startposition.
substring_length is specified and startposition < 1:
The substring contains (startposition + substring_length-1) characters (but not beyond the last character of string), beginning with the first character of string.
substring_length is not specified and startposition >= 1
The substring contains, as of startposition, all the characters in the string up to the last character.
substring_length is not specified and startposition < 1
The whole string is extracted.
Data type: If expression has the alphanumeric data type CHAR(n) or VARCHAR(n), the result has the alphanumeric data type VARCHAR(n).
If expression has the national data type NCHAR(n) or NVARCHAR(n), the result has the national data type NVARCHAR(n).
Examples
A substring is to be extracted from the string 'The Poodle Parlor'. 'The Poodle Parlor' is the company name of a customer in the CUSTOMERS table.
startposition is > 1, substring_length is specified:
SELECT SUBSTRING (company FROM 6 FOR 4) FROM customers WHERE cust_num=105
The result is the string 'Poodle'.
startposition is 0, substring_length is specified:
SELECT SUBSTRING (company FROM 0 FOR 5) FROM customers WHERE cust_num=105
The result is the string 'The' with a length of (0+4-1) = 3.
startposition is <0 and (startposition + substring_length -1) is greater than the length of string:
SELECT SUBSTRING (company FROM -2 FOR 20) FROM customers WHERE
cust_num=105
The result is the string 'The Poodle Parlor'.
startposition is > 1, substring_length is not specified:
SELECT SUBSTRING (company FROM 6) FROM customers WHERE cust_num=105
The result is the string 'Poodle Parlor'.
startposition is greater than the number of characters in string:
SELECT SUBSTRING (company FROM 15 FOR 5) FROM customers WHERE cust_num=105
The result is a string with a length of 0.