SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-20
CAST Expression
CAST Expression
Considerations for CAST
Valid Conversions for CAST
Examples of CAST
The CAST expression converts data to the data type you specify.
expression | NULL
specifies the operand to convert to the data type data-type.
If the operand is an expression, then data-type depends on the data type of
expression and follows the rules outlined in Valid Conversions for CAST.
If the operand is NULL, or if the value of the expression is null, the result of
CAST is NULL, regardless of the data type you specify.
data-type
specifies a data type to associate with the operand of CAST. See Data Types on
page 6-16.
When casting data to a CHAR or VARCHAR data type, the resulting data value is
left justified. Otherwise, the resulting data value is right justified. Further, when you
are casting to a CHAR or VARCHAR data type, you must specify the length of the
target value.
Considerations for CAST
Depending on how your file is set up, using CAST might cause poor query
performance by preventing the optimizer from choosing the most efficient plan and
requiring the executor to perform a complete table or index scan.
Valid Conversions for CAST
•
An exact or approximate numeric value to any other numeric data type.
•
An exact or approximate numeric value to any character string data type.
•
An exact numeric value to either a single-field year-month or day-time interval such
as INTERVAL ‘30’ DAY.
•
A character string to any other data type, with one restriction:
The contents of the character string to be converted must be consistent in meaning
with the data type of the result. For example, if you are converting to DATE, the
contents of the character string must be 10 characters consisting of the year, a
hyphen, the month, another hyphen, and the day.
CAST ({expression | NULL} AS data-type)