The SUBSTRING function

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";

Figure 6. Query result

sname           (expression)

Arizona         A