Numeric literals in DB2 SQL

Numeric literals overview

A numeric constant specifies a number as literal SQL text. Db2 further classifies numeric constants as:

• Integer constants
• Floating-point constants
• Decimal constants
• Decimal floating-point (DECFLOAT) constants

A negative sign in front of numeric zero is ignored. Constants other than NULL are NOT NULL. Mixing a numeric constant with a distinct type usually requires casting one side so assignment and comparison rules are satisfied.

Integer constants

An integer constant is a signed or unsigned number with a maximum of 19 digits and no decimal point.

1
VALUES 64, -15, +100, 32767, 720176;

Type selection follows magnitude:

• Large integer (INTEGER) — if the value fits in the large integer range
• Big integer (BIGINT) — if it exceeds large integer but fits BIGINT
• Decimal constant — if it is outside the BIGINT range

In syntax diagrams, the term integer often means a large integer constant that must not include a sign—for example certain clauses that expect a plain count. When a diagram says integer, do not write +5; write 5.

Integer literals are perfect for key values and counts. For money, prefer DECIMAL literals (or DECIMAL columns) so cents do not disappear into binary floating-point approximation.

Decimal constants

A decimal constant is a signed or unsigned number of no more than 31 digits that either includes a decimal point or is not within the range of binary integers.

1
VALUES 025.50, 1000., -15., +375893333333333333333.33;

Two attributes matter:

• Precision — total number of digits, including leading and trailing zeros
• Scale — number of digits to the right of the decimal point, including trailing zeros

So 025.50 has precision 5 and scale 2; 1000. has precision 4 and scale 0. Leading zeros surprise beginners: they count toward precision. That affects whether the constant fits a DECIMAL(p,s) target without overflow.

Floating-point constants

A floating-point constant writes a double-precision value as two numbers separated by E. The first number may have a sign and decimal point; the second may have a sign but not a decimal point. The value is the first number times ten to the power of the second.

1
2
VALUES 15E1, 2.E5, -2.2E-1, +5.E+2;
-- represent 150, 200000, -0.22, and 500

Limits include a maximum length in characters and digit counts in mantissa and exponent (see the SQL Reference). Floating-point is approximate: it is excellent for scientific measures and poor for exact currency. If you write '15E1' with quotes, you created a character string, not a number—another common beginner mistake.

Decimal floating-point constants

DECFLOAT constants also use an E form, but with room for much larger coefficient precision (up to DECFLOAT(34) rules). A constant with E becomes DECFLOAT when it is outside ordinary floating-point range. A number without E that has more than 31 digits is also a DECFLOAT constant.

1
2
VALUES 123456789012345678E0, SNAN, -INFINITY;
VALUES CAST('NAN' AS DECFLOAT), CAST('INF' AS DECFLOAT);

Special values

• INF or INFINITY — infinity (optional sign)
• NAN — quiet not-a-number
• SNAN — signaling not-a-number (can raise warnings/exceptions in numeric operations; non-numeric uses like INSERT VALUES may be quieter)

Special values are case-insensitive. When a token could be read as an identifier (for example a column named NAN), cast a string to DECFLOAT instead of using the bare special value. Comparison ordering among specials follows a documented precedence involving signed NAN/SNAN and infinities.

Hexadecimal literals (and what they are not)

Learners often say “hexadecimal numeric literal” when they mean one of two different things:

• X'...' — hexadecimal character-string constant (pairs of hex digits as character data)
• BX'...' — hexadecimal binary-string constant

1
2
3
4
VALUES X'0A';      -- character string, not INTEGER 10
VALUES BX'0A';     -- one-byte binary string
VALUES 10;         -- integer numeric literal
VALUES DECIMAL(X'0A');  -- only if conversion rules allow—prefer clear casts

Db2 does not treat X'0A' as the integer ten the way some languages treat 0x0A. If you need the number ten, write 10. If you need a byte, use binary types and BX, or store character hex intentionally and convert with documented functions or casts.

GX and UX forms are graphic hexadecimal strings, also not numeric types. Keep “hex as notation for bytes/characters” separate from “numeric constant categories” in your mental model—it prevents mysterious SQLCODE type errors.

Promotion, overflow, and practical tips

When an expression mixes numeric types, Db2 applies promotion and operation result rules. A tiny integer literal in a DECIMAL expression does not always keep “integer-ness.” Overflow can occur when assigning a wide constant into a narrow column.

• Prefer DECIMAL literals for money — 19.99 rather than 19.99E0
• Watch leading zeros — they increase decimal precision
• Do not quote numbers unless you truly want character data (then CAST)
• Use host variables for values that change between executions
• Match column scale — inserting 1.2 into DECIMAL(5,2) is fine; into DECIMAL(5,0) may round or fail depending on context and options

1
2
3
4
UPDATE HR.EMPLOYEE
   SET SALARY = SALARY * 1.03
 WHERE WORKDEPT = 'A00'
   AND SALARY > 50000;

Explain It Like I'm Five

Writing a number in SQL is like writing how many marbles you have on a scrap of paper. If you write 12, that is a whole-marble count (integer). If you write 12.50, you are counting marbles and half-marbles with exact tenths (decimal). If you write 1.25E1, you are using a science shortcut that means “about this many” (floating-point). Hex sticky notes like X'0A' are not marble counts—they are secret codes for letters or raw bricks. Use marble writing for numbers, and secret codes only when you mean bytes or characters.

Exercises

1. Classify each token: 100, 100., 1E2, '1E2', X'64', BX'64'.
2. Compute precision and scale for 007.6500.
3. Why is 0.1 + 0.2 in floating-point a bad way to total currency, and what literal style would you prefer?
4. Write a DECFLOAT infinity constant and a safe CAST form that cannot be mistaken for an identifier.
5. Explain to a teammate why X'0A' is not “hex ten” as an INTEGER.