ALLBASE/SQL Reference Manual (36216-90216)

240 Chapter8
Expressions
CAST Function
• If both source and target data type are character strings, the language of the result
string is the same as the source.
• If the source data type is a character string and the target data type is a numeric, then
the source value must only contain a character representation of a number. The result
of the conversion is the numeric value that string represented.
If the source value is not a numeric string, an error occurs.
• If the target data type is CHAR(n), and the source data type is an exact numeric, the
result is a character representation of that exact numeric. If the source value is less
than zero, the first character of the result is a minus sign. Otherwise, the first character
is a number or a decimal point.
If the length of the resulted string is less than n, then blanks are added on the right. If
the length of the resulted string is greater than n, an error occurs. The same algorithm
applies if the target data type is VARCHAR(n), except that there is no need to pad the
numeric string if its length is less than n.
• If the target data type is CHAR(n) and the source data type is an approximate numeric,
then the number is converted to a character representation in scientific notation.
If the length of the resulted string is less than n, then blanks are added on the right. If
the length of the resulted string is greater than n, then an error occurs. The same
algorithm applies if the target data type is VARCHAR(n), except that there is no need
to pad the numeric string if its length is less than n.
• Conversion between character and binary data types is supported implicity as well as
with CAST. The same rules still apply with CAST. If a target is shorter than the source,
truncation occurs. If the target is larger than the source, the target is zero-filled in the
case of BINARY(n), and blank-filled in the case of CHAR(n).
• When converting a non-character data type to BINARY(n) or VARBINARY(n), the data
is not modified. Only the type changes so that the data is treated as binary data. The
size of the source and the target in bytes must be equal in the case of BINARY(n), and
the size of the source must be less than or equal to the size of the target in the case of
VARBINARY(n). Otherwise, an error occurs.
For decimal numbers, each digit of precision contributes 4 bits and 4 bits for the sign.
The overall size is rounded up to a 4-byte boundary. The storage size for DATE, TIME,
DATETIME, and INTERVAL is 16 bytes.
• When converting from BINARY(n) or VARBINARY(n) into a non-character data type,
the data is not modified. Only the type changes so that the data is treated as a number
of the target data type. The actual size of the source and the target in bytes must be
equal, or an error occurs.
• Conversion between binary data types and numeric data types is an ALLBASE
extension and is not allowed according to the ANSI SQL2 standard.
• Converting a character string to a DATE, TIME, DATETIME or INTERVAL with CAST
is equivalent to using the respective date/time function, TO_DATE, TO_TIME,
TO_DATETIME, or TO_INTERVAL. All the same rules apply.
• Using CAST to convert numeric types directly to date/time types is not allowed. This