Db2 NULL vs empty string

NULL vs empty string

IBM defines the null value as a special indicator meaning no data is present— unknown or missing. It is distinct from every non-null value, including strings that contain only spaces or have length zero.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- NULL: unknown middle name
INSERT INTO EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME)
VALUES ('000010', 'CHRISTINE', NULL, 'HAAS');
 
-- Empty VARCHAR: known to be blank/absent text, but not null
UPDATE PROFILE
   SET NICKNAME = ''
 WHERE EMPNO = '000010';
 
-- Find unknowns (not empty strings)
SELECT EMPNO, LASTNAME
  FROM EMP
 WHERE MIDINIT IS NULL;

MIDINIT IS NULL does not match a column that holds spaces or ''. Conversely, NICKNAME = '' does not match NULL. If your report under-counts “missing” nicknames, you are probably mixing these concepts.

Three-valued logic reminder

Comparing NULL with = or <> yields unknown, not true. Rows drop out of WHERE unless you use IS NULL / IS NOT NULL, or rewrite with COALESCE when a shop standard maps nulls to a display sentinel.

Empty CHAR vs VARCHAR

VARCHAR(n) stores a length and the characters used. Length can be zero: a true empty string. That value still occupies the length prefix; it is a real non-null value unless the column is null.

CHAR(n) always has length n. If you assign a short string, Db2 pads with blanks on the right. There is no CHAR value whose length attribute is zero. When people say “empty CHAR,” they almost always mean all blanks (or they wrongly stored NULL instead).

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE DEMO_EMPTY (
    CODE_CH   CHAR(5),
    CODE_VC   VARCHAR(5)
);
 
INSERT INTO DEMO_EMPTY (CODE_CH, CODE_VC) VALUES ('', '');
-- CODE_CH becomes five blanks; CODE_VC can be length 0
 
SELECT CODE_CH, LENGTH(CODE_CH) AS LEN_CH,
       CODE_VC, LENGTH(CODE_VC) AS LEN_VC
  FROM DEMO_EMPTY;

LENGTH on a blank CHAR(5) reports 5 (characters), not 0. LENGTH on an empty VARCHAR reports 0. Display tools that trim blanks for readability can hide the difference—look at length and hex dumps when debugging.

Defaults

When Db2 supplies a default for a varying-length string column (NOT NULL WITH DEFAULT without a custom value), the default is typically the empty string. Fixed-length string defaults are typically blanks. Neither case is NULL unless the column allows nulls and you insert null.

Padding and comparison surprises

String comparison rules interact with padding. Fixed-length values are blank-padded for comparison in ways that make 'A' in CHAR(5) compare equal to a shorter character string of 'A' in many assignment/comparison situations—while VARCHAR trailing blanks have their own rules that confuse people moving from other databases.

• CHAR pads — short assignments grow with spaces; storage always shows width n
• VARCHAR length matters — 'A' and 'A ' can be different stored lengths even when some comparisons treat them carefully
• TRIM / STRIP — use deliberately when you must normalize before compare
• LIKE patterns — blanks and empty strings behave differently from NULL; LIKE never “matches null”

1
2
3
4
5
6
7
8
9
-- Dangerous if MIDINIT uses blanks for “none” in some rows and NULL in others
SELECT EMPNO FROM EMP WHERE MIDINIT = '';
SELECT EMPNO FROM EMP WHERE MIDINIT IS NULL;
SELECT EMPNO FROM EMP WHERE MIDINIT = ' ';  -- single blank, not “empty CHAR(1)” story alone
 
-- Safer pattern: pick one representation and stick to it
SELECT EMPNO FROM EMP
 WHERE MIDINIT IS NULL
    OR LENGTH(STRIP(MIDINIT)) = 0;

The last pattern can be expensive and still wrong if blanks are meaningful. Prefer a clean design: nullable column with NULL for unknown, or NOT NULL with a documented default—not both conventions in one column.

Common beginner mistakes

• Assuming SELECT * display equals storage — trimmed UI hides CHAR pads
• Porting from databases where '' and NULL collapse — Db2 keeps them distinct
• Using NOT NULL everywhere then stuffing blanks — you lose “unknown” forever
• Forgetting host null indicators — COBOL sees spaces while SQLCODE is fine, but the indicator said null
• UNIQUE indexes and blanks — multiple all-blank CHAR keys collide; multiple nulls have separate uniqueness rules

Shop standard checklist

Agree in writing: (1) when to use NULL, (2) whether CHAR codes may be blank, (3) whether VARCHAR empty strings are allowed, (4) how screens display each case. Then write predicates and host code to that standard.

Explain It Like I'm Five

A lunchbox can be missing (NULL—we don’t know if there is lunch), present but empty (VARCHAR with nothing inside), or a fixed-size box stuffed with napkins so it always looks full size (CHAR blanks). Asking “is the lunchbox missing?” is different from “is there no food inside?” Mixing those questions makes you miss your sandwich.

Exercises

1. Write two SELECTs: one finding NULL comments, one finding zero-length VARCHAR comments.
2. Explain why CHAR(3) cannot store a true length-zero value.
3. Give one reason a report counted fewer “missing emails” than expected when authors used = '' only.
4. Propose a column rule for MIDDLE_NAME: NULL, blanks, or empty VARCHAR—and justify it.
5. What goes wrong if a COBOL program ignores the null indicator and always displays the host field?