Constants

User-defined types have strong typing, except for weakly typed distinct types.. This means that a strongly typed user-defined type is only compatible with its own type. A constant, however, has a built-in type. Therefore, an operation involving a strongly typed 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_DB_ROSTER
     WHERE AGE > CAST(14 AS YOUTH)

   SELECT * FROM CAMP_DB_ROSTER
     WHERE CAST(AGE AS INTEGER) > 14

The following comparison is not valid:

   SELECT * FROM CAMP_DB_ROSTER
     WHERE AGE > 14

Examples:

   64     -15     +100     32767     720176      12345678901

Examples:

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

Examples:

   25.5     1000.     -15.     +37589.3333333333

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 numeric 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-numeric 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) 

A character string constant specifies a varying-length character string. The constant value string units are determined by the environment default string units. 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. When the environment string unit is CODEUNITS32, the number of code units cannot be greater than 8 168. 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. When the environment string unit is CODEUNITS32, the number of code units that the hexadecimal constant represents cannot be greater than 8 168. Two consecutive string delimiters are used to represent one string delimiter within the character string. A hexadecimal digit is a digit 0 through 9 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. When the environment string unit is CODEUNITS32, the number of code units that the Unicode string constant represents cannot be greater than 8 168. 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.

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.

A graphic string constant specifies a varying-length graphic string.

Non-Unicode databases

In a non-Unicode database, a graphic string constant consists 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 double bytes. The apostrophe must not appear as part of an MBCS character to be considered a delimiter. For example:

   G'double-byte character string'
   N'double-byte character string'

Unicode databases

In a Unicode database, a graphic string constant consists of a sequence of characters that starts and ends with an apostrophe ('), and that is preceded by a G or N character. The constant value string units are determined by the environment default string units. The characters between the apostrophes are converted to code page 1200 and the length of the graphic string must not exceed 16 336 double bytes. When the environment string unit is CODEUNITS32, the number of code units must not exceed 8 168.

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.

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.

Example:

   UX'0042006F006200620079'

represents the VARGRAPHIC pattern of the ASCII string 'Bobby'.