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 ü 2
Code 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.

Was this tutorial helpful ?