Trailing blanks in CHAR/VARCHAR

Trailing blanks in CHAR/VARCHAR database columns

With all kinds of databases servers, CHAR columns are always filled with blanks up to the size of the column (this is called blank padding). However, trailing blanks are not significant in comparisons:

CHAR('abc ') = CHAR('abc')

With all database servers except IBM^® Informix^®, trailing blanks are significant when comparing VARCHAR values:

VARCHAR('abc ') != VARCHAR('abc')

This is a major issue if you mix CHAR and VARCHAR columns and variables in your SQL statements, because the result of an SQL query can be different depending on whether you are using IBM Informix or another database server.

Further, the semantics of the SQL LIKE operator regarding trailing blanks and CHAR/VARCHAR types can differ from database to database. For example, try the following expressions with you database, with a CHAR(5) column containing a row with the value 'abc':

CREATE TABLE t1 ( k INT, c CHAR(5), vc VARCHAR(5) )
INSERT INTO t1 VALUES ( 1, 'abc', 'abc' )
SELECT * FROM t1 WHERE c LIKE 'ab_'
SELECT * FROM t1 WHERE vc LIKE 'ab_' 
SELECT * FROM t1 WHERE RTRIM(c) LIKE 'ab_'
SELECT * FROM t1 WHERE c LIKE '%c'
SELECT * FROM t1 WHERE vc LIKE '%c'
SELECT * FROM t1 WHERE RTRIM(c) LIKE '%c'

See discussion about MATCHES and LIKE operators in adaption guides for more details.

Trailing blanks in CHAR/VARCHAR program variables

In programs, CHAR variables are filled with blanks, even if the value used does not contain all spaces.

The following example:

DEFINE c CHAR(5)
LET c = "abc"
DISPLAY c || "."

shows the value "abc ." (5 chars + dot).

VARCHAR variables are assigned with the exact value specified, with significant trailing blanks.

For example, this code:

DEFINE v VARCHAR(5)
LET v = "abc "
DISPLAY v || "."

shows the value "abc ." (4 chars + dot).

Assigning an empty string to a CHAR or VARCHAR variable will set the variable to NULL:

DEFINE v VARCHAR(5)
LET v = ""
IF v IS NULL THEN
   DISPLAY "is null"   -- will be displayed
END IF

When comparing CHAR or VARCHAR variables in an expression, the trailing blanks are not significant:

DEFINE c CHAR(5)
DEFINE v1, v2 VARCHAR(5)
LET c = "abc"
LET v1 = "abc "
LET v2 = "abc  "
IF c == v1 THEN
   DISPLAY "c==v1" 
END IF
IF c == v2 THEN
   DISPLAY "c==v2" 
END IF
IF v1 == v2 THEN
   DISPLAY "v1==v2" 
END IF

All three messages are shown.

Additionally, when you assign a VARCHAR variable from a CHAR, the target variable gets the trailing blanks of the CHAR variable:

DEFINE pc CHAR(50)
DEFINE pv VARCHAR(50)
LET pc = "abc"
LET pv = pc
DISPLAY pv || "."

"abc <47 spaces>. " ( 50 chars + dot ) is shown.

To avoid this, use the CLIPPED operator:

LET pv = pc CLIPPED

Trailing blanks in SQL statement parameters

When you insert a row containing a CHAR variable into a CHAR or VARCHAR column, the database interface removes the trailing blanks to avoid overflow problems, (insert CHAR(100) into CHAR(20) when value is "abc" must work).

In this example:

DEFINE c CHAR(5)
LET c = "abc"
CREATE TABLE t ( v1 CHAR(10), v2 VARCHAR(10) )
INSERT INTO tab VALUES ( c, c )

The value in column v1 and v2 would be "abc" ( 3 chars in both columns ).

When you insert a row containing a VARCHAR variable into a VARCHAR column, the VARCHAR value in the database gets the trailing blanks as set in the variable. When the column is a CHAR(N), the database server fills the value with blanks so that the size of the string is N characters.

In this example:

DEFINE vc VARCHAR(5)
LET vc = "abc  "  -- note 2 spaces at end of string
CREATE TABLE t ( v1 CHAR(10), v2 VARCHAR(10) )
INSERT INTO tab VALUES ( vc, vc )

The value in column v1 would be "abc " ( 10 chars ) and v2 would be "abc " (5 chars ).