SUBSTR scalar function

The SUBSTR function returns a substring of a string.

The schema is SYSIBM.

string-expression

An expression that specifies the string from which the result is derived. The string must be a character, graphic, or binary string. If string-expression is a character string, the result of the function is a character string. If it is a graphic string, the result of the function is a graphic string. If it is a binary string, the result of the function is a binary string.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

A substring of string-expression is zero or more contiguous characters of string-expression. If string-expression is a graphic string, a character is a DBCS character. If string-expression is a character string or a binary string, a character is a byte. The SUBSTR function accepts mixed data strings. However, because SUBSTR operates on a strict byte-count basis, the result will not necessarily be a properly formed mixed data string.

start

An expression that specifies the position within string-expression to be the first character of the result. The value of the large integer must be between 1 and the length attribute of string-expression. (The length attribute of a varying-length string is its maximum length.) A value of 1 indicates that the first character of the substring is the first character of string-expression.

The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34) which is then assigned to an INTEGER value.

length

An expression that specifies the length of the resulting substring. If specified, length must be an expression that returns a value that is a built-in large integer data type.

The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34) which is then assigned to an INTEGER value.

The value must be greater than or equal to 0 and less than or equal to n, where n is the length attribute of

string-expression - start +
1

. The specified length must not, however, be the large integer constant 0.

If length is explicitly specified, string-expression is effectively padded on the right with the necessary number of characters so that the specified substring of string-expression always exists. Hexadecimal zeros are used as the padding character when string-expression is binary data. Otherwise, a blank is used as the padding character.

If string-expression is a fixed-length string, omission of length is an implicit specification of LENGTH(string-expression) - start + 1, which is the number of characters (or bytes) from the character (or byte) specified by start to the last character (or byte) of string-expression. If string-expression is a varying-length string, omission of length is an implicit specification of the greater of zero or LENGTH(string-expression) - start + 1. If the resulting length is zero, the result is an empty string.

If length is explicitly specified by a large integer constant that is 255 or less, and string-expression is not a LOB, the result is a fixed-length string with a length attribute of length. If length is not explicitly specified, but string-expression is a fixed-length string and start is an integer constant, the result is a fixed-length string with a length attribute equal to LENGTH(string-expression) - start + 1. In all other cases, the result is a varying-length string. If length is explicitly specified by a large integer constant, the length attribute of the result is length; otherwise, the length attribute of the result is the same as the length attribute of string-expression.

The result can be null; if any argument is null, the result is the null value.

The CCSID of the result is the CCSID of string-expression.

Example 1: FIRSTNME is a VARCHAR(12) column in sample table DSN8D10.EMP. When FIRSTNME has the value 'MAUDE':

   Function:                 Returns:
   -----------------------------------
   SUBSTR(FIRSTNME,2,3)      -- 'AUD'
   SUBSTR(FIRSTNME,2)        -- 'AUDE'
   SUBSTR(FIRSTNME,2,6)      -- 'AUDE' followed by two blanks
   SUBSTR(FIRSTNME,6)        -- a zero-length string
   SUBSTR(FIRSTNME,6,4)      -- four blanks

Example 2: Sample table DSN8D10.PROJ contains column PROJNAME, which is defined as VARCHAR(24). Select all rows from that table for which the string in PROJNAME begins with 'W L PROGRAM'.

   SELECT * FROM DSN8D10.PROJ
     WHERE SUBSTR(PROJNAME,1,12) = 'W L PROGRAM ';

Assume that the table has only the rows that were supplied by Db2. Then the predicate is true for just one row, for which PROJNAME has the value 'W L PROGRAM DESIGN'. The predicate is not true for the row in which PROJNAME has the value 'W L PROGRAMMING' because, in the predicate's string constant, 'PROGRAM' is followed by a blank.

Example 3: Assume that a LOB locator named my_loc represents a LOB value that has a length of 1 gigabyte. Assign the first 50 bytes of the LOB value to host variable PORTION.

   SET :PORTION = SUBSTR(:my_loc,1,50);

Example 4: Assume that host variable RESUME has a CLOB data type and holds an employee's resume. This example shows some of the statements that find the section of department information in the resume and assign it to host variable DeptBuf. First, the POSSTR function is used to find the beginning and ending location of the department information. Within the resume, the department information starts with the string 'Department Information Section' and ends immediately before the string 'Education Section'. Then, using these beginning and ending positions, the SUBSTR function assigns the information to the host variable.

   SET :DInfoBegPos = POSSTR(:RESUME, 'Department Information Section');
   SET :DInfoEnPos = POSSTR(:RESUME, 'Education Section');
   SET :DeptBuf = SUBSTR(:RESUME, :DInfoBegPos, :DInfoEnPos - :DInfoBegPos);