What is a substring

The SUBSTRING function

Returns the characters extracted from a string based on the specified character position for a specified number of characters.

The character position and number of characters are based on the number of characters, not bytes. Therefore, characters with multiple bytes are counted as a single character. You cannot specify a negative length. However, you can specify a negative starting position.

syntax

Argument

string

The string to search. Non-character data types are treated as a string.

start_position

The position within the string at which to start the extraction, starting with 1. The start_position is based on the number of characters, not bytes. Therefore, characters with multiple bytes are counted as a single character. This number can be negative.

number_characters

The number of characters to extract (the length of the substring). The number_characters is based on the number of characters, not bytes. Therefore, characters with multiple bytes are counted as a single character. This number cannot be negative.

Return type

VARCHAR

Usage instructions

The following example returns a string of four characters, starting with the sixth character.

If start_position + number_characters the length of the String SUBSTRING returns a substring start_position back to the end of the string. Example:

If negative or 0, the SUBSTRING function returns a substring from the first character of the string with length + -1. Example:

If + -1 is equal to or less than zero, SUBSTRING returns an empty string. Example:

Examples

The following example returns the month from the LISTTIME string in the LISTING table:

The following example does the same thing as shown above, but with the FROM ... FOR option:

You cannot use SUBSTRING to predictably extract the prefix of a string that may contain multibyte characters because you need to specify the length of a multibyte string by the number of bytes, not the number of characters. To extract the starting segment of a string based on its length in bytes, you can use the string in (byte_length) to truncate the string, where byte_length is the required length. The following example extracts the first 5 bytes from the string.