<< Click to Display Table of Contents >> Navigation:  Local SQL > Data Manipulation Language > Functions > SUBSTRING function

Extracts a substring from a string.

SUBSTRING(column_reference FROM start_index [FOR length])

Description

Use SUBSTRING to extract a substring from a character value (column, literal, parameter, or caculated values), specified in the column reference.

FROM is the character position at which the extracted substring starts within the original string. The index for FROM is based on the first character in the source value being 1.

FOR is optional, and specifies the length of the extracted substring. If FOR is omitted, the substring goes from the position specified by FROM to the end of the string.

The example below, applied to the literal string "ABCDE" returns the value "BCD".

SELECT SUBSTRING("ABCDE" FROM 2 FOR 3) AS Sub

FROM Country

In the SELECT statement below only the second and subsequent characters of the NAME column are retrieved. For a column value of "Belgium" that would be a "elgium" result.

SELECT SUBSTRING(Name FROM 2)

FROM Country

When applied to retrieved data of a SELECT statement, the effect is transient and does not affect stored data. When applied to the update atoms of an UPDATE statement, the effect is persistent and permanently converts the case of the stored values.

Applicability

SUBSTRING can only be used with character columns or literals. To use on values of other data types, the values must first be converted to CHAR using the CAST function.

Note: the SUBSTRING function cannot be used with memo or BLOB columns.