Constants
A constant (also called a literal) specifies a value. Constants are classified as null constants, string constants, numeric constants, or datetime constants. Numeric constants are further classified as integer, floating-point, decimal, or decimal floating-point. String constants are classified as character, graphic, or binary.
All constants, except null constants, have the attribute NOT NULL. A negative sign in a numeric constant with a value of zero is ignored.
Constants, other than null constants, have a built-in data type. Therefore, an operation that involves a constant and a distinct type requires that the distinct type be cast to the built-in data type of the constant or the constant be cast to the distinct type. For example, see User-defined type comparisons, which contains an example of casting data types to compare a constant to a distinct type.
Null constants
A null constant indicates the null value.
The NULL constant has no data type. The data type of the NULL keyword is provided by the context in which it is used, or you can use CAST to assign a data type.
Integer constants
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 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 a 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.
For example, the following values are integer constants:
64 -15 +100 32767 720176In syntax diagrams, the term integer is used for a large integer constant that must not include a sign.
Floating-point constants
A floating-point constant specifies a double-precision floating-point number as two numbers separated by an E.
The first number can include a sign and a decimal point. The second number can include a sign but not a decimal point. 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. Excluding leading zeros, the number of digits in the first number must not exceed 17 and the number of digits in the second must not exceed 2.
15E1 2.E5 -2.2E-1 +5.E+2Decimal constants
A decimal constant is a signed or unsigned number of no more than 31 digits and either includes a decimal point or is not within the range of binary integers.
The precision is the total number of digits, including those, if any, to the right of the decimal point. The total includes all leading and trailing zeros. The scale is the number of digits to the right of the decimal point, including trailing zeros.
The precision is the total number of digits, including those, if any, to the right of the decimal point. The total includes all leading and trailing zeros. The scale is the number of digits to the right of the decimal point, including trailing zeros.
025.50 1000. -15. +375893333333333333333.33Decimal floating-point constant
A decimal floating-point constant specifies a decimal floating-point number as two numbers separated by an E. The first number can include a sign and a decimal point. The second number can include a sign but not a decimal point.
The value of the constant is the product of the first number and the power of 10 specified by the second number. The value must be within the range of DECFLOAT(34). The number of characters in the constant must not exceed 42. Excluding leading zeros, the number of digits in the first number must not exceed 34 and the number of digits in the second number must not exceed 4.
A constant that is specified as two numbers separated by an E is a decimal-floating point constant only if the value is outside the range of a floating-point constant. A constant that is specified as a number that does not contain an E, and has more than 31 digits, is also a decimal-floating point constant.
In addition to numeric constants, the following special values can be used to specify decimal-floating point special values:
- INF or INFINITY - represents infinity
- NAN - represents quiet not-a-number
- SNAN - represents signaling not-a-number
The special values can be any combination of uppercase or lowercase letters and can be preceded by an operational sign (+ or -).
SNAN results in a warning or exception when it is used in a numerical operation; NAN does not. SNAN can be used in non-numerical operations without causing a warning or exception. For example, SNAN can be used in the VALUES list of an insert operation or as a constant used in a comparison in a predicate.
When the special values are used in a predicate, the following order of precedence applies:
-NAN < -SNAN < -INFINITY < -0 < 0 < INFINITY < SNAN < NAN 123456789012345678E0 SNAN -INFINITYWhen one of the special values is used in a context where it could be interpreted as an identifier, such as a column name, cast a string constant that represents the special value to decimal-floating point.
CAST ('snan' AS DECFLOAT)
CAST ('INF' AS DECFLOAT)
CAST ('Nan' AS DECFLOAT) Character string constants
A character string constant specifies a varying-length character string. A character string constant has one of the two following forms:
- A sequence of characters that starts and ends with a string delimiter, which is either an apostrophe (') or a quotation mark ("). For the factors that determine which is applicable, see Apostrophes and quotation marks as string delimiters. This form of string constant specifies the character string contained between the string delimiters. The number of bytes between the delimiters must not be greater than 32704. The limit of 32704 refers to the length (in bytes) of the UTF-8 representation of the string. If you produced the string in a CCSID other than UTF-8 (for example, an EBCDIC CCSID), the length of the UTF-8 representation might differ from the length of the string's representation in the source CCSID. Two consecutive string delimiters are used to represent one string delimiter within the character string.
- An X followed by a sequence of characters that starts and ends with a string delimiter. This form of a character string constant is also called a hexadecimal constant. The characters between the string delimiters must be an even number of hexadecimal digits. The number of hexadecimal digits must not exceed 32704. A hexadecimal digit is a digit or any of the letters A through F. If the MIXED DATA subsystem parameter is set to YES, hexadecimal digits in a hexadecimal constant must be specified in upper case. Otherwise, an error might be returned when SQL statements are processed. Under the conventions of hexadecimal notation, each pair of hexadecimal digits represents a character. A hexadecimal constant allows you to specify characters that do not have a keyboard representation.
The following examples are character strings constants. The right most string in the example ('') represents an empty character string constant, which is a string of zero length.
'12/14/1985' '32' 'DON''T CHANGE' X'FFFF' ''A character string constant is classified as mixed data if it includes a DBCS substring. In all other cases, a character string constant is classified as SBCS data. For information about the CCSID that is assigned to the constant, see Determining the encoding scheme and CCSID of a string. A mixed string constant can be continued from one line to the next only if the break occurs between single byte characters. A Unicode string is always considered mixed regardless of the content of the string.
For Unicode, character constants can be assigned to UTF-8 and UTF-16. The form of the constant does not matter. Typically, character string constants are used only with character strings, but they also can be used with graphic UTF-16 data. However, hexadecimal constants are just character data. Thus, hexadecimal constants being used to insert data into UTF-16 data strings should be in UTF-8 format, not UTF-16 format. For example, if you wanted to insert the number 1 into a UTF-16 column, you would use X'31', not X'0031'. Even though X'0031' is a UTF-16 value, Db2 treats it as two separate UTF-8 code points. Thus, X'0031' would become X'00000031'.
Binary string constants
A binary-string constant specifies a varying-length binary string.
A binary-string constant is formed by specifying a BX followed by a sequence of characters that starts and ends with a string delimiter. The characters between the string delimiters must be an even number of hexadecimal digits. The number of hexadecimal digits must not exceed 32704.
A hexadecimal digit is a digit or any of the letters A through F (upper case or lower case). Under the conventions of hexadecimal notation, each pair of hexadecimal digits represents one byte. Note that this representation is similar to the representation of the character-constant that uses the X'' form; however binary-string constant and character-string constant are not compatible and the X'' form can not be used to specify a binary-string constant, just as the BX'' form cannot be used to specify a character-string constant.
he following examples are binary-string constants:
BX'0000' BX'C141C242' BX'FF00FF01FF'Graphic string constants
A graphic string constant specifies a varying-length graphic string. For more information, see Graphic string constants.
Datetime constants
A datetime constant is a character string constant of a particular format. For more information, see Datetime constants