A constant (sometimes called a literal) specifies a value. Constants are classified as string constants or numeric constants. Numeric constants are further classified as integer, floating-point, or decimal.
All constants have the attribute NOT NULL.
A negative zero value in a numeric constant (-0) is the same value as a zero without the sign (0).
An integer constant specifies an integer as a signed or unsigned number with a maximum of 19 digits that does not include a decimal point. The data type of an integer constant is a large integer if its value is within the range of a large integer. The data type of an integer constant is big integer if its value is outside the range of large integer but within the range of a big integer. A constant that is defined outside the range of big integer values is considered a decimal constant.
Note that the smallest literal representation of a large integer constant is -2 147 483 647 and not -2 147 483 648, which is the limit for integer values. Similarly, the smallest literal representation of a big integer constant is -9 223 372 036 854 775 807 and not -9 223 372 036 854 775 808 which is the limit for big integer values.
Examples
64 -15 +100 32767 720176 12345678901
In syntax diagrams the term 'integer' is used for a large integer constant that must not include a sign.
A floating-point constant specifies a floating-point number as two numbers separated by an E. The first number may include a sign and a decimal point; the second number may include a sign but not a decimal point. The data type of a floating-point constant is double precision. The value of the constant is the product of the first number and the power of 10 specified by the second number; it must be within the range of floating-point numbers. The number of characters in the constant must not exceed 30.
Examples
15E1 2.E5 2.2E-1 +5.E+2
A decimal constant is a signed or unsigned number that consists of no more than 31 digits and either includes a decimal point or is not within the range of binary integers. It must be in the range of decimal numbers. The precision is the total number of digits (including leading and trailing zeros); the scale is the number of digits to the right of the decimal point (including trailing zeros).
Examples
25.5 1000. -15. +37589.3333333333
A character string constant specifies a varying-length character string and consists of a sequence of characters that starts and ends with an apostrophe ('). This form of string constant specifies the character string contained between the string delimiters. The length of the character string must not be greater than 32 672 bytes. Two consecutive string delimiters are used to represent one string delimiter within the character string.
Examples
'12/14/1985' '32' 'DON''T CHANGE'
The constant value is always converted to the database code page when it is bound to the database. It is considered to be in the database code page. Therefore, if used in an expression that combines a constant with a FOR BIT DATA column, of which the result is FOR BIT DATA, the constant value will not be converted from its database code page representation when used.
A hexadecimal constant specifies a varying-length character string with the code page of the application server.
The format of a hexadecimal string constant is an X followed by a sequence of characters that starts and ends with an apostrophe (single quote). The characters between the apostrophes must be an even number of hexadecimal digits. The number of hexadecimal digits must not exceed 16 336, otherwise an error is raised (SQLSTATE -54002). A hexadecimal digit represents 4 bits. It is specified as a digit or any of the letters A through F (uppercase or lowercase) where A represents the bit pattern '1010', B the bit pattern '1011', etc. If a hexadecimal constant is improperly formatted (e.g. it contains an invalid hexadecimal digit or an odd number of hexadecimal digits), an error is raised (SQLSTATE 42606).
Examples
X'FFFF' representing the bit pattern '1111111111111111' X'4672616E6B' representing the VARCHAR pattern of the ASCII string 'Frank'
A graphic string constant specifies a varying-length graphic string and consists of a sequence of double-byte characters that starts and ends with a single-byte apostrophe (') and is preceded by a single-byte G or N. This form of string constant specifies the graphic string contained between the string delimiters. The length of the graphic string must be an even number of bytes and must not be greater than 16 336 bytes.
Examples:
G'double-byte character string'
N'double-byte character string'
The apostrophe must not appear as part of an MBCS character to be considered a delimiter.
User-defined types have strong typing. This means that a user-defined type is only compatible with its own type. A constant, however, has a built-in type. Therefore, an operation involving a user-defined type and a constant is only possible if the user-defined type has been cast to the constant's built-in type or the constant has been cast to the user-defined type (see CAST Specifications for information on casting). For example, using the table and distinct type in User-defined Type Comparisons, the following comparisons with the constant 14 are valid:
SELECT * FROM CAMP_DB2_ROSTER
WHERE AGE > CAST(14 AS YOUTH)
SELECT * FROM CAMP_DB2_ROSTER
WHERE CAST(AGE AS INTEGER) > 14
The following comparison is not valid:
SELECT * FROM CAMP_DB2_ROSTER
WHERE AGE > 14