substring Function

The substring function extracts a string from a given string according to a given numeric starting position and a given numeric substring length.

Syntax

returnvalue substring (source, position [, substring_length] )

source ::= any*
position ::= integer*
substring_length ::= integer*
returnvalue ::= string

Semantics

source

The input string from which the substring should be extracted. This argument is implicitly cast to a sequence of strings.

position

This argument indicates the starting point of the substring within the source. The first character of the source string has position 0.

An error is thrown if a non-integer value is supplied for the position.

substring_length

This argument indicates the length of the substring starting from the position value. If the supplied value is greater than the length of the source, then the length of the source is assumed for this argument.

An error is thrown if a non-integer value is supplied for the substring_length.

returnvalue

Returns an empty string ("") if the function did not return any characters.

Returns an empty string ("") if the substring_length is less than 1.

Returns NULL if the source argument is NULL.

Returns NULL if the position argument is less than 0 or greater or equal to the source length.

Example 12-45 substring Function

In this example, the first character in the firstname is selected from the users table. Notice that to select the first character, we have provided the value 0 for the position argument.

SELECT substring(firstname,0,1) as Initials FROM users

Output:

 +----------+
 | Initials |
 +----------+
 | J        |
 | P        |
 | M        |
 +----------+

Example 12-46 substring Function

This example illustrates that providing a negative value for the position argument will result in a NULL output value.

SELECT substring (firstname, -5, 4) FROM users

Output:

 +----------+
 | Column_1 |
 +----------+
 | NULL     |
 | NULL     |
 | NULL     |
 +----------+

Example 12-47 substring Function

In this example, we select the first 4 characters from the firstname in the users table.

SELECT substring (firstname, 0, 4) FROM users

Output:

 +----------+
 | Column_1 |
 +----------+
 | John     |
 | Pete     |
 | Mary     |
 +----------+

Example 12-48 substring Function

In this example, we select 100 characters starting from position 2. Notice that even though none of the rows has more than 5 characters in firstname, still we get the output up to the length of the source starting from position 2.

SELECT substring (firstname, 2, 100) FROM users

Output:

 +----------+
 | Column_1 |
 +----------+
 | hn       |
 | ter      |
 | ry       |
 +----------+

Example 12-49 substring Function

In this example, the substring_length argument is not provided as it is optional. In such cases, we get the complete substring starting from the given position.

SELECT substring (firstname, 2) FROM users

Output:

 +----------+
 | Column_1 |
 +----------+
 | hn       |
 | ter      |
 | ry       |
 +----------+