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.

What you will learn in this post

• 1. You Will Learn Syntax for Substring Function

• 2. Example on How to Use Substring

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

Substring example

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 OCTETS- Specifies 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.
• CODEUNITS16 – Specifies that start and length are expressed in terms of 16-bit UTF-16 code units.
• CODEUNITS32 – Specifies that start and length are expressed in terms of 32-bit UTF-32 code units.
• OCTETS – Specifies that start and length are expressed in terms of bytes.

Related Posts