Db2 CHAR

Summary: in this tutorial, you will learn how to use Db2 CHAR data type to store fixed-length character string in the database.

Introduction to Db2 CHAR data type

The CHARACTER or CHAR data type allows you to store fixed-length character strings in the database.

To declare a column of the CHAR type, you use the following syntax:

CHAR(n)
Code language: SQL (Structured Query Language) (sql)

It is equivalent to the following syntax:

CHARACTER(n)
Code language: SQL (Structured Query Language) (sql)

In this syntax, n is the maximum number of bytes that a CHAR column can store. The range of n is 1 to 255; it defaults to 1.

Notice that if you store Unicode characters,  one character may take more than one byte.

Db2 CHAR type examples

Let’s create a new table named db2_characters to store the fixed length character strings:

CREATE TABLE db2_characters(
    char_col CHAR(3)
);
Code language: SQL (Structured Query Language) (sql)

1) Insert a fixed-length character string into the CHAR column example

First, insert a string into the CHAR column:

INSERT INTO db2_characters(char_col)
VALUES('abc');
Code language: SQL (Structured Query Language) (sql)

Then, query data from the db2_characters table:

SELECT * FROM db2_characters;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

CHAR_COL
--------
abc
Code language: SQL (Structured Query Language) (sql)

2) Padding space before inserting example

The following statement inserts the string xy whose length is 2 into the CHAR(3) column:

INSERT INTO db2_characters(char_col)
VALUES('xy');
Code language: SQL (Structured Query Language) (sql)

In this case, Db2 padded space to the string xy to make the length of the string 3 before inserting it into the CHAR(3) column.

To verify it, you can use the CHARACTER_LENGTH() function to get the number of characters of each value in the char_col column:

SELECT 
    char_col, 
    CHARACTER_LENGTH(char_col) length 
FROM
    db2_characters;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

CHAR_COL      LENGTH
-------- -----------
abc                3
xy                 3

3) Inserting a Unicode character string into CHAR column example

The following statement inserts a Unicode character into the CHAR(3) column:

INSERT INTO db2_characters(char_col) 
VALUES('ü');Code language: SQL (Structured Query Language) (sql)

The character ü takes two bytes in UTF-8.

SELECT 
   char_col, 
   CHARACTER_LENGTH(char_col) length 
FROM 
   db2_characters;Code language: SQL (Structured Query Language) (sql)

Here is the result set of the query:

CHAR_COL      LENGTH
-------- -----------
abc                3
xy                 3
ü                  2Code language: SQL (Structured Query Language) (sql)

In this tutorial, you’ve learned about Db2 CHAR data type and how to use it to store fixed-length character strings in the database.