SUBSTR scalar function

Syntax

Description

string

An expression that specifies the string from which the result is derived.

If the value is not a string data type, it is implicitly cast to VARCHAR before the function is evaluated. A substring of string is zero or more contiguous string units of string .

start

This expression must be a numeric data type representing the first character position of the substring within the search string.

If the value is not of type INTEGER, it is implicitly cast to INTEGER before the function is evaluated. The integer value must be between 1 and the length or maximum length of string, depending on whether string is fixed-length or varying-length (SQLSTATE 22011, if out of range).

length

An expression that specifies the length of the result. If provided, this expression must be a numeric data type representing the length of the substring. If not provided, the substring is all characters from the first character position of the substring within the search string to the end of the search string.

If the value is not of type INTEGER, it is implicitly cast to INTEGER before the function is evaluated. The value of the integer must be in the range of 0 to n, where n equals the following calculation:

(the length attribute of string in string units) - start + 1

If the length is out of range, you receive SQLSTATE 22011.

The result can be null if any argument is null.

The result data type is the same as the input data type.

Usage notes

If you explicitly specify a value for the length, the value of string is effectively padded on the right with the necessary number of blank characters, so that the specified substring of string always exists.

Examples

The following function returns a VARCHAR(6) with a value of 'CD':

SELECT SUBSTR('ABCDEF',3,2) FROM t1;

The following example selects all rows from the return reason table that start with the string Wrong.


SELECT *
 FROM gosalesdw.dist_return_reason_dim 
 WHERE SUBSTR(reason_description_en, 1,5)='Wrong';