You can use the SUBSTRING function
to return some portion of a character string. You specify the start
position and length (optional) to determine which portion
of the character string the function returns. You can specify a positive
or negative number for the start position. A start position of 1 specifies
that the SUBSTRING function begins from the first
position in the string. When the start position is zero (0) or a negative
number, the SUBSTRING function counts backward
from the beginning of the string.
The following query shows
an example of the SUBSTRING function, which returns
the first four characters for any sname column values that
the query returns. In this example, the SUBSTRING function
starts at the beginning of the string and returns four characters
counting forward from the start position.Figure 1: Query
SELECT sname, SUBSTRING(sname FROM 1 FOR 4) FROM state
WHERE code = "AZ";
Figure 2: Query result
sname (expression)
Arizona Ariz
In the following query, the SUBSTRING function
specifies a start position of 6 but does not specify
the length. The function returns a character string that extends from
the sixth position to the end of the string. Figure 3: Query
SELECT sname, SUBSTRING(sname FROM 6) FROM state
WHERE code = "WV";
Figure 4: Query result
sname (expression)
West Virginia Virginia
In the following query, the SUBSTRING function
returns only the first character for any sname column value
that the query returns. For the SUBSTRING function,
a start position of -2 counts backward three positions
(0, -1, -2) from the start position of the string
(for a start position of 0, the function counts backward
one position from the beginning of the string). Figure 5: Query
SELECT sname, SUBSTRING(sname FROM -2 FOR 4) FROM state
WHERE code = "AZ";