String literals in DB2 SQL

SQL literals (constants) in brief

A constant (also called a literal) specifies a value directly in the SQL text. Db2 classifies constants as:

• Null constants — the keyword NULL
• String constants — character, graphic, or binary
• Numeric constants — integer, decimal, floating-point, decimal floating-point
• Datetime constants — date/time/timestamp written as special string forms (covered on the next datetime-literals page)

All constants except null constants have the attribute NOT NULL. That matters when you compare types and when you reason about predicates: the string '' (empty string) is not the same as NULL. An empty character string is a real zero-length value; NULL means “unknown / absent.”

Constants have built-in data types. If you mix a constant with a distinct (user-defined) type, you usually need an explicit CAST so the types match the comparison or assignment rules.

Character string literals

A character string constant is a varying-length character string written in one of two common ways:

• Delimited text — characters between string delimiters (apostrophe ' or quotation mark ", depending on host language and SQL string delimiter options)
• Hexadecimal character form — X followed by a delimited even-length hex digit sequence

Ordinary delimited strings

1
2
SELECT '12/14/1985', '32', 'DON''T CHANGE', ''
  FROM SYSIBM.SYSDUMMY1;

Rules beginners must memorize:

• Double the delimiter to include one delimiter inside the string (DON''T inside apostrophe-delimited text)
• Length limit — the UTF-8 representation of the content must not exceed 32704 bytes (source EBCDIC length can differ after conversion)
• Empty string — '' is a valid zero-length character constant
• Mixed / DBCS content — a constant that includes a DBCS substring is classified as mixed data; otherwise it is typically SBCS. Unicode strings are treated as mixed regardless of content

In COBOL programs, precompiler options such as APOST versus QUOTE decide whether SQL string delimiters are apostrophes or quotation marks. Match the option your shop uses or the precompiler will reject literals that “look fine” in a textbook that assumed the other delimiter.

Hexadecimal character constants (X'...')

The X'...' form lets you specify characters by hexadecimal digit pairs. Digits are 0–9 and A–F. The count of hex digits must be even and within the documented maximum. Under hexadecimal notation, each pair represents one character byte.

1
2
3
-- Example shapes (values depend on CCSID / encoding)
VALUES X'FFFF';
VALUES X'C1C2C3';   -- often EBCDIC 'ABC' on z/OS

When the MIXED DATA subsystem parameter is YES, hexadecimal digits in a hexadecimal constant must be specified in uppercase or you may get errors when statements are processed. Prefer uppercase hex digits in portable examples.

Hex character constants are still character data, not binary. That distinction bites people who insert into UTF-16 columns: Db2 may treat the hex as UTF-8 code points. For UTF-16 oriented graphic data, prefer the UX form described below rather than assuming X'0031' means Unicode digit one the way you expect.

Graphic string literals

Graphic strings hold double-byte oriented data. Constants can appear as delimited graphic forms (environment-dependent, including PL/I-style forms in static SQL) or as hexadecimal graphic forms:

• GX'xxxx' — groups of four hex digits as graphic characters in the application encoding scheme (ASCII/EBCDIC). Not for Unicode encoding schemes; if MIXED DATA is NO, use UX instead
• UX'xxxx' — UTF-16 Unicode graphic characters, CCSID 1200. Example: UX'004100420043' for ABC

1
VALUES UX'004100420043';  -- UTF-16 graphic 'ABC'

Graphic constants cannot always be continued across source lines the same way ordinary character constants can—keep them short enough and on one line when the rules require it. CCSID assignment follows encoding-scheme rules documented for string constants; mismatched CCSID assumptions are a common source of “looks wrong in QMF” bugs.

Binary string literals

A binary-string constant uses BX followed by a delimited even-length hex digit sequence. Each pair of digits is one byte. Examples:

1
2
3
VALUES BX'0000';
VALUES BX'C141C242';
VALUES BX'FF00FF01FF';

Critical compatibility rule: BX'...' and X'...' are not interchangeable. Binary strings (BINARY, VARBINARY, BLOB family) are compatible with other binary strings. Character hex constants are character data. Using the wrong prefix is a type error waiting to happen.

Prefer true binary types and BX literals for raw bytes. Older “character FOR BIT DATA” columns blur the line for legacy applications; new designs should keep character and binary separate.

Choosing the right form

Practical guidance:

• Human-readable text — ordinary 'text' literals
• Bytes hard to type — X for character contexts, BX for binary columns
• UTF-16 graphic samples — UX
• Never use a string literal when you mean NULL — write NULL, or use indicators / COALESCE patterns in programs

Literals in predicates and programs

String literals appear constantly in WHERE clauses:

1
2
3
4
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
   AND LASTNAME LIKE 'S%';

In host languages, prefer host variables over embedding business values as literals inside dynamic SQL strings you build by concatenation. Literals in static SQL are fine for fixed codes; concatenating user input into dynamic SQL invites injection mistakes and defeats statement caching benefits. Bind variables keep types clear and plans reusable.

Also remember CCSID: the constant takes a CCSID from application encoding rules. Comparing a Unicode column to an EBCDIC literal can force conversions. When results look “almost right,” check encoding before rewriting the business logic.

Explain It Like I'm Five

A string literal is like writing a word on a sticky note and handing it to Db2. If the word itself needs a quote mark, you write the quote mark twice so Db2 knows the sticky note is not finished early. Sometimes instead of letters you write secret code (hex) that means the same bytes—like saying “draw the letter by its number.” BX sticky notes are for raw toy bricks (binary). Ordinary and X sticky notes are for readable words (character). UX sticky notes are for special double-wide international letter tiles.

Exercises

1. Write a character literal that stores the text: It's a "quoted" test. (Pick the delimiter style your shop uses and escape correctly.)
2. Explain why '' is not the same as NULL when filtering a VARCHAR column.
3. Decide whether X'C1' or BX'C1' belongs in an INSERT into a VARBINARY column, and why.
4. Convert the letters ABC into a UX'...' constant for UTF-16.
5. Find which string delimiter (apostrophe vs quote) your COBOL precompiler options use at your site.