DB2 Version 9.7 for Linux, UNIX, and Windows

Constants

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 NOT NULL attribute.

A negative zero value in a numeric constant (-0) is the same value as a zero without the sign (0).

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 if the constant has been cast to the user-defined type. 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

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 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.

Floating-point constants

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 bytes in the constant must not exceed 30.

Examples:
   15E1     2.E5     2.2E-1     +5.E+2

Decimal constants

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 within 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

Decimal floating-point constants

There are no decimal floating-point constants except for the decimal floating-point special values, which are interpreted as DECFLOAT(34).

These special values are: INFINITY, NAN, and SNAN. INFINITY represents infinity, a number whose magnitude is infinitely large. INFINITY can be preceded by an optional sign. INF can be specified in place of INFINITY. NAN represents Not a Number (NaN) and is sometimes called quiet NaN. It is a value that represents undefined results which does not cause a warning or exception. SNAN represents signaling NaN (sNaN). It is a value that represents undefined results which will cause a warning or exception if used in any operation that is defined in any numerical operation. Both NAN and SNAN can be preceded by an optional sign, but the sign is not significant for arithmetic operations.. SNAN can be used in non-numerical operations without causing a warning or exception, for example in the VALUES list of an INSERT or as a constant compared in a predicate.
   SNAN   -INFINITY
When one of the special values (INFINITY, INF, NAN, or SNAN) is used in a context where it could be interpreted as an identifier, such as a column name, cast a string representation of the special value to decimal floating-point. Examples:
   CAST ('snan' AS DECFLOAT)
   CAST ('INF' AS DECFLOAT)
   CAST ('Nan' AS DECFLOAT) 

All non-special values are interpreted as integer, floating-point or decimal constants, in accordance with the rules specified above. To obtain a numeric decimal floating-point value, use the DECFLOAT cast function with a character string constant. It is not recommended to use floating-point constants as arguments to the DECFLOAT function, because floating-point is not exact and the resulting decimal floating-point value might be different than the decimal digit characters that make up the argument. Instead, use character constants as arguments to the DECFLOAT function.

For example, DECFLOAT('6.0221415E23', 34) returns the decimal floating-point value 6.0221415E+23, but DECFLOAT(6.0221415E23, 34) returns the decimal floating-point value 6.0221415000000003E+23.

Character string constants

A character string constant specifies a varying-length character string. There are three forms of a character string constant:
  • A sequence of characters that starts and ends with a string delimiter, which is an apostrophe ('). The number of bytes between the string delimiters cannot be greater than 32 672. Two consecutive string delimiters are used to represent one string delimiter within the character string. Two consecutive string delimiters that are not contained within a string represent the empty string.
  • 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. Blanks between the string delimiters are ignored. The number of hexadecimal digits must not exceed 32 672. A hexadecimal digit is a digit or any of the letters A through F (uppercase or lowercase). Under the conventions of hexadecimal notation, each pair of hexadecimal digits represents a character. This form of a character string constant allows you to specify characters that do not have a keyboard representation.
  • U& followed by a sequence of characters that starts and ends with a string delimiter and that is optionally followed by the UESCAPE clause. This form of a character string constant is also called a Unicode string constant. The number of bytes between the string delimiters cannot be greater than 32 672. The Unicode string constant is converted from UTF-8 to the section code page during statement compilation. Two consecutive string delimiters are used to represent one string delimiter within the character string. Two consecutive Unicode escape characters are used to represent one Unicode escape character within the character string, but these characters count as one character when calculating the lengths of character constants. Two consecutive string delimiters that are not contained within a string represent the empty string. Because a character in UTF-8 can range from 1 to 4 bytes, a Unicode string constant of the maximum length might actually represent fewer than 32 672 characters.

    A character can be expressed by either its typographical character (glyph) or its Unicode code point. The code point of a Unicode character ranges from X'000000' to X'10FFFF'. To express a Unicode character through its code point, use the Unicode escape character followed by 4 hexadecimal digits, or the Unicode escape character followed by a plus sign (+) and 6 hexadecimal digits. The default Unicode escape character is the reverse solidus (\), but a different character can be specified with the UESCAPE clause. The UESCAPE clause is specified as the UESCAPE keyword followed by a single character between string delimiters. The Unicode escape character cannot be a plus sign (+), a double quotation mark ("), a single quotation mark ('), a blank, or any of the characters 0 through 9 or A through F, in either uppercase or lowercase (SQLSTATE 42604). An example of the two ways in which the Latin capital letter A can be specified as a Unicode code point is \0041 and \+000041.

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, and whose result is FOR BIT DATA, the constant value will not be converted from its database code page representation when used.

Examples:
'12/14/1985'    '32'    'DON''T CHANGE'    ''
X'FFFF'    X'46 72 61 6E 6B'
U&'\0141ód\017A is a city in Poland'   U&'c:\\temp'   U&'@+01D11E' UESCAPE '@'
The rightmost string on the second line in the example represents the VARCHAR pattern of the ASCII string 'Frank'. The last line corresponds to: 'Łódź is a city in Poland', 'c:\temp', and a single character representing the musical symbol G clef.

Graphic string constants

A graphic string constant specifies a varying-length graphic string consisting of a sequence of double-byte characters that starts and ends with a single-byte apostrophe ('), and that is preceded by a single-byte G or N. The characters between the apostrophes must represent an even number of bytes, and the length of the graphic string must not exceed 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.

In a Unicode database, a hexadecimal graphic string constant that specifies a varying-length graphic string is also supported. The format of a hexadecimal graphic string constant is: GX followed by a sequence of characters that starts and ends with an apostrophe. The characters between the apostrophes must be an even multiple of four hexadecimal digits. The number of hexadecimal digits must not exceed 16 336; otherwise, an error is returned (SQLSTATE 54002). If a hexadecimal graphic string constant is improperly formed, an error is returned (SQLSTATE 42606). Each group of four digits represents a single graphic character. In a Unicode database, this would be a single UCS-2 graphic character.

Examples:
   GX'FFFF'
represents the bit pattern '1111111111111111' in a Unicode database.
   GX'005200690063006B'
represents the VARGRAPHIC pattern of the ASCII string 'Rick' in a Unicode database.

Datetime constants

A datetime constant specifies a date, time, or timestamp.

Typically, character-string constants are used to represent constant datetime values in assignments and comparisons. However, the associated data type name can be used preceding specific formats of the character-string constant to specifically denote the constant as a datetime constant instead of a character-string constant. The format for the three datetime constants are:
DATE 'yyyy-mm-dd'
The data type of the value is DATE.
TIME 'hh:mm:ss'
or
TIME 'hh:mm'
The data type of the value is TIME.
TIMESTAMP 'yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn'
or
TIMESTAMP 'yyyy-mm-dd-hh.mm.ss.nnnnnnnnnnnn'
where the number of digits of fractional seconds can vary from 0 to 12 and the period character can be omitted if there are no fractional seconds. The data type of the value is TIMESTAMP(p), where p is the number of digits of fractional seconds.
Leading zeros can be omitted from the month, day, and hour part of the character-string constant portion, where applicable, in each of these datetime constants. Leading zero characters must be included for minutes and seconds elements of TIME or TIMESTAMP constants. Trailing blanks can be included and are ignored.

UCS-2 graphic string constants

In a Unicode database, a hexadecimal UCS-2 graphic string that specifies a varying-length UCS-2 graphic string constant is supported. The format of a hexadecimal UCS-2 graphic string constant is: UX followed by a sequence of characters that starts and ends with an apostrophe. The characters between the apostrophes must be an even multiple of four hexadecimal digits. The number of hexadecimal digits must not exceed 16 336; otherwise, an error is returned (SQLSTATE 54002). If a hexadecimal UCS-2 graphic string constant is improperly formed, an error is returned (SQLSTATE 42606). Each group of four digits represents a single UCS-2 graphic character.

Example:
   UX'0042006F006200620079'
represents the VARGRAPHIC pattern of the ASCII string 'Bobby'.

Boolean constants

A Boolean constant specifies the keyword TRUE or FALSE, representing the truth values true and false respectively. The unknown truth value can be specified using CAST(NULL AS BOOLEAN).