Db2 VARCHAR

Summary: in this tutorial, you’ll learn how to use the Db2 VARCHAR data type to store variable-length character strings.

Overview of Db2 VARCHAR type

Db2 VARCHAR type is used to store variable-length character strings. To define a variable-length character string column, you use the following syntax:

column_name VARCHAR(n)
Code language: SQL (Structured Query Language) (sql)

In this syntax, n is a positive integer that represents the maximum length of n bytes that the column can store. n also must be greater than zero and less than 32,740.

If you need to store a string whose length is longer than this, you should use the VARBINARY(n) data type instead.

Db2 VARCHAR type examples

Let’s create a new table to demonstrate the characteristics of VARCHAR(n) data type.

This statement creates a new table named db2_varchars that has a VARCHAR(20) column:

CREATE TABLE db2_varchars (
	v VARCHAR(20) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

1) Insert a string into a variable-length character string column example

The following example inserts a string into the v column of the db2_varchars table:

INSERT INTO db2_varchars (v)
VALUES
	('Db2 Tutorial');
Code language: SQL (Structured Query Language) (sql)

It worked as expected because the string Db2 Tutorial has the length which is less than 20.

However, this statement attempts to insert a string whose length is 22 into the VARCHAR(20) column and fails:

INSERT INTO db2_varchars(v)
VALUES
	('A Guide to Db2 VARCHAR');
Code language: SQL (Structured Query Language) (sql)

Db2 issued the following error message:

SQL0433N  Value "A Guide to Db2 VARCHAR" is too long.
Code language: SQL (Structured Query Language) (sql)

2) Insert a Unicode string into a variable-length character string column example

This example inserts a Unicode string die Prüfung into the VARCHAR(20) column of the db2_varchars table:

INSERT INTO db2_varchars(v)
VALUES
	('die Prüfung');
Code language: SQL (Structured Query Language) (sql)

To view the data in the db2_varchars table, you use the following SELECT statement:

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

Here is the output:

V                   
--------------------
Db2 Tutorial        
die Prüfung
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 VARCHAR data type to store variable-length character strings.