The best Substring example in Db2 for Quick use

The syntax is as follows for DB2 substring. Any function returns some value. When substring is not returned any value, you can say it as ‘Zero length string’, but not to say as it is NULL. The answer to this question you need to tell very nicely. 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.

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.

 

Advertisements

Author: Srini

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