DB2 Version 9.7 for Linux, UNIX, and Windows

SUBSTRING scalar function


>>-SUBSTRING--(------------------------------------------------->

>--+-expression--FROM--start--+-------------+--USING--+-CODEUNITS16-+-+-><
   |                          '-FOR--length-'         +-CODEUNITS32-+ |   
   |                                                  '-OCTETS------' |   
   '-expression--,--start--+-----------+--,--+-CODEUNITS16-+--)-------'   
                           '-,--length-'     +-CODEUNITS32-+              
                                             '-OCTETS------'

The schema is SYSIBM.

The SUBSTRING function returns a substring of a string.

expression

An expression that specifies the string from which the result is derived. The expression must return a value that is a built-in string, numeric, or datetime data type. If the value is not a string data type, it is implicitly cast to VARCHAR before evaluating the function. If expression is a character string, the result is a character string. If expression is a graphic string, the result is a graphic string. If expression is a binary string, the result is a binary string.

A substring of expression is zero or more contiguous string units of expression.

start

An expression that specifies the position within expression that is to be the first string unit of the result. The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The value of the integer can be positive, negative, or zero; a value of 1 indicates that the first string unit of the result is the first string unit of expression. If OCTETS is specified and expression is graphic data, the value of the integer must be odd; otherwise, an error is returned (SQLSTATE 428GC).

length

The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function.

If expression is a fixed-length string, omission of length is an implicit specification of CHARACTER_LENGTH(expression USING string-unit) - start + 1, which is the number of string units (CODEUNITS16, CODEUNITS32, or OCTETS) from start to the last position of expression. If expression is a varying-length string, omission of length is an implicit specification of zero or CHARACTER_LENGTH(expression USING string-unit) - start + 1, whichever is greater. If the desired length is zero, the result is the empty string.

If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The value must be greater than or equal to zero. If a value greater than n is specified, where n is the (length attribute of expression) - start + 1, then n is used as the length of the resulting substring. The value is expressed in the units that are explicitly specified. If OCTETS is specified, and expression is graphic data, the value must be an even number (SQLSTATE 428GC).

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the string unit of start and length. CODEUNITS16 specifies that start and length are to be expressed in 16-bit UTF-16 code units. CODEUNITS32 specifies that start and length are to be expressed in 32-bit UTF-32 code units. OCTETS specifies that start and length are to be expressed in bytes.

If a string unit is specified as CODEUNITS16 or CODEUNITS32, and expression is a binary string or bit data, an error is returned (SQLSTATE 428GC). If a string unit is specified as OCTETS and expression is a binary string, an error is returned (SQLSTATE 42815).

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see "String units in built-in functions" in "Character strings".

When the SUBSTRING function is invoked using OCTETS, and the source-string is encoded in a code page that requires more than one byte per code point (mixed or MBCS), the SUBSTRING operation might split a multi-byte code point and the resulting substring might begin or end with a partial code point. If this occurs, the function replaces the bytes of leading or trailing partial code points with blanks in a way that does not change the byte length of the result. (See a related example below.)

The length attribute of the result is equal to the length attribute of expression. If any argument of the function can be null, the result can be null; if any argument is null, the result is the null value. The result is not padded with any character. If expression has actual length 0, the result also has actual length 0.

Notes:

The length attribute of the result is equal to the length attribute of the input string expression. This behavior is different from the behavior of the SUBSTR function, where the length attribute is derived from the start and the length arguments of the function.

Examples:

FIRSTNAME is a VARCHAR(12) column in table T1. One of its values is the 6-character string 'Jürgen'. When FIRSTNAME has this value:

Function ...                          Returns ...
------------------------------------  ------------------------------------
SUBSTRING(FIRSTNAME,1,2,CODEUNITS32)  'Jü' -- x'4AC3BC'
SUBSTRING(FIRSTNAME,1,2,CODEUNITS16)  'Jü' -- x'4AC3BC'
SUBSTRING(FIRSTNAME,1,2,OCTETS)       'J ' -- x'4A20' (a truncated string)
SUBSTRING(FIRSTNAME,8,CODEUNITS16)    a zero-length string
SUBSTRING(FIRSTNAME,8,4,OCTETS)       a zero-length string

The following example illustrates how SUBSTRING replaces the bytes of leading or trailing partial multi-byte code points with blanks when the string length unit is OCTETS. Assume that UTF8_VAR contains the UTF-8 representation of the Unicode string '&N~AB', where '&' is the musical symbol G clef and '~' is the combining tilde character.
```
   SUBSTRING(UTF8_VAR, 2, 5, OCTETS)
```
Three blank bytes precede the 'N', and one blank byte follows the 'N'.