The SUBSTR function

The SUBSTR function serves the same purpose as the SUBSTRING function, but the syntax of the two functions differs.

To return a portion of a character string, specify the start position and length (optional) to determine which portion of the character string the SUBSTR function returns. The start position that you specify for the SUBSTR function can be a positive or a negative number. However, the SUBSTR function treats a negative number in the start position differently than does the SUBSTRING function. When the start position is a negative number, the SUBSTR function counts backward from the end of the character string, which depends on the length of the string, not the character length of a word or visible characters that the string contains. The SUBSTR function recognizes zero (0) or 1 in the start position as the first position in the string.

The following query shows an example of the SUBSTR function that includes a negative number for the start position. Given a start position of -15, the SUBSTR function counts backward 15 positions from the end of the string to find the start position and then returns the next five characters.

Figure 1: Query

SELECT sname, SUBSTR(sname, -15, 5) FROM state 
   WHERE code = "CA";

Figure 2: Query result

sname           (expression)

California      Calif

To use a negative number for the start position, you need to know the length of the value that is evaluated. The sname column is defined as CHAR(15), so a SUBSTR function that accepts an argument of type sname can use a start position of 0, 1, or -15 for the function to return a character string that begins from the first position in the string.

The following query returns the same result as Query.

Figure 3: Query

SELECT sname, SUBSTR(sname, 1, 5) FROM state 
   WHERE code = "CA";