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–(charecter expression, start, Length, CODEUNITS16 or CODEUNITS32 or 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 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

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.