Substring in DB2 Quick Tutorial

Substring function in DB2 plays critical role when you need to deal with Strings.

The syntax is for DB2 substring given in the below a ready to use code.

Normally a function returns some value, but substring does not return any value- you can say it as ‘Zero length string’, but not to say as it is NULL.

This is actually a typical question. The other functions in DB2 you can read before you move on.

>>-SUBSTRING--(charecter expression, start, Length, CODEUNITS16
                                                    --
                                                     CODEUNITS32
                                                    --
                                                     OCTETS)

The example for substring

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

   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

More explanation

CODEUNITS16, CODEUNITS32, or OCTETSSpecifies the string unit that is used to express start and length. If character-expression is a character string that is defined as bit data, CODEUNITS16 and CODEUNITS32 cannot be specified.

CODEUNITS16Specifies that start and length are expressed in terms of 16-bit UTF-16 code units.

CODEUNITS32Specifies that start and length are expressed in terms of 32-bit UTF-32 code units.

OCTETSSpecifies that start and length are expressed in terms of bytes.