SQL Reference

SUBSTR

>>-SUBSTR--(--string--,--start----+-------------+--)-----------><
                                  '-,--length---'
 

The schema is SYSIBM.

The SUBSTR function returns a substring of a string.

If string is a character string, the result of the function is a character string represented in the code page of its first argument. If it is a binary string, the result of the function is a binary string. If it is a graphic string, the result of the function is a graphic string represented in the code page of its first argument. If any argument of the SUBSTR function can be null, the result can be null; if any argument is null, the result is the null value.

string

An expression that specifies the string from which the result is derived.

If string is either a character string or a binary string, a substring of string is zero or more contiguous bytes of string. If string is a graphic string, a substring of string is zero or more contiguous double-byte characters of string.

start

An expression that specifies the position of the first byte of the result for a character string or a binary string or the position of the first character of the result for a graphic string. start must be an integer between 1 and the length or maximum length of string, depending on whether string is fixed-length or varying-length (SQLSTATE 22011, if out of range). It must be specified as number of bytes in the context of the database code page and not the application code page.

length

An expression that specifies the length of the result. If specified, length must be a binary integer in the range 0 to n, where n equals (the length attribute of string) - start + 1 (SQLSTATE 22011, if out of range).

If length is explicitly specified, string is effectively padded on the right with the necessary number of blank characters (single-byte for character strings; double-byte for graphic strings) so that the specified substring of string always exists. The default for length is the number of bytes from the byte specified by the start to the last byte of string in the case of character string or binary string or the number of double-byte characters from the character specified by the start to the last character of string in the case of a graphic string. However, if string is a varying-length string with a length less than start, the default is zero and the result is the empty string. It must be specified as number of bytes in the context of the database code page and not the application code page. (For example, the column NAME with a data type of VARCHAR(18) and a value of 'MCKNIGHT' will yield an empty string with SUBSTR(NAME,10)).

Table 16 shows that the result type and length of the SUBSTR function depend on the type and attributes of its inputs.

Table 16. Data Type and Length of SUBSTR Result

String Argument Data Type	Length Argument	Result Data Type
CHAR(A)	constant (l<255)	CHAR(l)
CHAR(A)	not specified but start argument is a constant	CHAR(A-start+1)
CHAR(A)	not a constant	VARCHAR(A)
VARCHAR(A)	constant (l<255)	CHAR(l)
VARCHAR(A)	constant (254<l<32673)	VARCHAR(l)
VARCHAR(A)	not a constant or not specified	VARCHAR(A)
LONG VARCHAR	constant (l<255)	CHAR(l)
LONG VARCHAR	constant (254<l<4001)	VARCHAR(l)
LONG VARCHAR	constant (l>4000)	LONG VARCHAR
LONG VARCHAR	not a constant or not specified	LONG VARCHAR
CLOB(A)	constant (l)	CLOB(l)
CLOB(A)	not a constant or not specified	CLOB(A)
GRAPHIC(A)	constant (l<128)	GRAPHIC(l)
GRAPHIC(A)	not specified but start argument is a constant	GRAPHIC(A-start+1)
GRAPHIC(A)	not a constant	VARGRAPHIC(A)
VARGRAPHIC(A)	constant (l<128)	GRAPHIC(l)
VARGRAPHIC(A)	constant (127<l<16337)	VARGRAPHIC(l)
VARGRAPHIC(A)	not a constant	VARGRAPHIC(A)
LONG VARGRAPHIC	constant (l<128)	GRAPHIC(l)
LONG VARGRAPHIC	constant (127<l<2001)	VARGRAPHIC(l)
LONG VARGRAPHIC	constant (l>2000)	LONG VARGRAPHIC
LONG VARGRAPHIC	not a constant or not specified	LONG VARGRAPHIC
DBCLOB(A)	constant (l)	DBCLOB(l)
DBCLOB(A)	not a constant or not specified	DBCLOB(A)
BLOB(A)	constant (l)	BLOB(l)
BLOB(A)	not a constant or not specified	BLOB(A)

If string is a fixed-length string, omission of length is an implicit specification of LENGTH(string) - start + 1. If string is a varying-length string, omission of length is an implicit specification of zero or LENGTH(string) - start + 1, whichever is greater.

Examples:

• Assume the host variable NAME (VARCHAR(50)) has a value of 'BLUE JAY' and the host variable SURNAME_POS (int) has a value of 6.

    SUBSTR(:NAME, :SURNAME_POS):ehp2s
  

  Returns the value 'JAY'

    SUBSTR(:NAME, :SURNAME_POS,1)
  

  Returns the value 'J'.

• Select all rows from the PROJECT table for which the project name (PROJNAME) starts with the word 'OPERATION'.

    SELECT * FROM PROJECT
      WHERE SUBSTR(PROJNAME,1,10) = 'OPERATION '
  

  The space at the end of the constant is necessary to preclude initial words such as 'OPERATIONS'.

Notes:

1. In dynamic SQL, string, start, and length may be represented by a parameter marker (?). If a parameter marker is used for string, the data type of the operand will be VARCHAR, and the operand will be nullable.

2. Though not explicitly stated in the result definitions above, it follows from these semantics that if string is a mixed single- and multi-byte character string, the result may contain fragments of multi-byte characters, depending upon the values of start and length. That is, the result could possibly begin with the second byte of a double-byte character, and/or end with the first byte of a double-byte character. The SUBSTR function does not detect such fragments, nor provides any special processing should they occur.