Attached is an article that I wrote on the CAST function that was added in
ALLBASE/SQL A.G1.15 which is now available as a beta patch. This of course
also applied to IMAGE/SQL. The following article is also on the web under
'http://jazz.external.hp.com/papers/Communicator/dbG1/cast/cast.html'.
Doug
----------------------------------------------------------------------------
Doug Myers
Hewlett-Packard Company (MS 47UX) e-mail: [log in to unmask]
19111 Pruneridge Avenue phone: (408) 447-6756
Cupertino, CA USA 95014-9974 FAX: (408) 447-7902
----------------------------------------------------------------------------
Explicit Data Type Conversion (CAST)
By Doug Myers, HP Database Lab
====================================
With the G1.15 release, the CAST function enhancement is now available to
customers. The CAST function is used to explicitly convert data from one
data type to another. The CAST function not only allows conversion between
compatible data types, such as between CHAR and BINARY or between INTEGER and
DECIMAL, but it will also allow conversion between certain normally
incompatible types, such as between CHAR and INTEGER.
The CAST function is defined in the ANSI SQL2 standard. CAST in ALLBASE/SQL
and IMAGE/SQL complies with that standard. In addition, several extensions to
the standard specification have been added to make CAST even more powerful.
The CAST function can be used anywhere a general expression is allowed. Also,
as a part of this enhancement, the SQL parser has been enhanced to allow
general expressions in more of the SQL syntax. For example, general
expressions including nested functions are now allowed in all the date/time
functions and string functions. Therefore, CAST will be supported inside
functions that supports expressions including aggregate functions. CAST will
also take general expressions including nested functions as input.
CAST Syntax
-----------
CAST ( {<expr>} {AS} <datatype> [, <formatspec>] )
{ NULL } {, }
<expr> can be a column, USER function, host variable, local variable,
AddMonths function, aggregate function, date/time conversion function, dynamic
parameter, or procedure parameter, constant, current function, long column
function, string function, or any combination of these in an arithmetic or
concatenation expression
<datatype> is an ALLBASE/SQL data type: CHAR(n), VARCHAR(n),
DECIMAL(p[,s]), FLOAT, REAL, INTEGER, SMALLINT, DATE, TIME, DATETIME,
INTERVAL, BINARY(n), VARBINARY(n). The LONG BINARY(n) and LONG VARBINARY(n)
can not be used in the CAST operations.
<formatspec> is for DATE, TIME, DATETIME, INTERVAL conversion. <formatspec>
is the same as that used in the date/time conversion functions. See the
ALLBASE/SQL Reference manual for more details.
The following table shows what the CAST function will support:
|
| target data type
----------------------------------------------------------------------
source |
data type| EN AN VC C FB VB D T DT I TD
----------------------------------------------------------------------
EN | +Y +Y *Y *Y *E *E N N N N N
AN | +Y +Y *Y *Y *E *E N N N N N
VC | *Y *Y +Y +Y +Y +Y +Y +Y +Y +Y *E
C | *Y *Y +Y +Y +Y +Y +Y +Y +Y +Y *E
B | *E *E +Y +Y +Y +Y *E *E *E *E *E
VB | *E *E +Y +Y +Y +Y *E *E *E *E *E
D | +E +E +Y +Y *E *E +Y N N N N
T | +E +E +Y +Y *E *E N +Y N N N
DT | +E +E +Y +Y *E *E N N +Y N N
I | +Y +E +Y +Y *E *E N N N +Y N
TD | N N *E *E *E *E N N N N +Y
EN = Exact Numeric(SMALLINT, INT[EGER], DEC[IMAL][(p[,s])],
NUMERIC[(p[,s])])
AN = Approximate Numeric(DEC[IMAL][(p[,s])], NUMERIC[(p[,s])],
FLOAT[(p)] or DOUBLE PRECISION, REAL)
C = CHAR(n)
VC = VARCHAR(n)
B = BINARY(n)
VB = VARBINARY(n)
D = DATE
T = TIME
DT = DATETIME
I = INTERVAL
TD = TID
E = ALLBASE/SQL Extension (not a part of ANSI standard)
N = No
Y = Yes
* = Newly implemented with Cast
+ = Partially or Entirely implemented in ALLBASE/SQL already.
If input to CAST is NULL, then the result of the CAST operation is NULL.
ALLBASE/SQL supports implicit data conversion between numeric
data types to numeric data types, character data types to character
data types, binary data types to binary data types, binary data types
to character data types, character data types to binary data types.
When CAST is used to do these conversions, all existing
rules will be applied.
When a number of greater precision is converted to a number of
lesser precision, if the number will not fit within the target
precision, an overflow error is given.
When converting from a approximate numeric to an exact numeric or
from a exact numeric to an exact numeric with less scale (integers
have a scale of 0), the extra digits of scale beyond the target scale
are dropped without rounding the result.
If both source and target data type are character strings, the
language of the result string will be the same as the source.
If the source data type is character string and target data type is numeric,
then the source value must only contain a character representation of a
number. If the source value is not a numeric string, an error will be sent to
user. The result of the conversion is numeric string becomes numeric type.
If target data type is CHAR(n), and source data type is exact numeric, the
result will be a character representation of that exact numeric. If the
source value is less than zero, the first character of the result will be a
minus sign. Otherwise, the first character will be a number or the decimal
point. If the length of resulted string is less than n, then blanks are added
on the right. If the length of resulted string is greater than n, an error
will be given. The same algorithm will apply if the target data type is
VARCHAR(n), except that no need to pad the numeric string if its length is
less than n.
If target data type is CHAR(n) and source data type is approximate numeric,
then the number will be converted to a character representation in scientific
notation. If the length of resulted string is less than n, then blanks are
added on the right, if the length of resulted string is greater than n, then
an error will occur. The same algorithm will apply to the target data type is
VARCHAR(n), except that no need to pad the numeric string if its length is
less than n.
Conversion between character and binary data types was already supported
before the CAST enhancement. The same rules still apply with CAST. If
a target is shorter than the source, truncation will occur. If the target
is larger than the source, the target will be 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 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 will
occur. 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 the data is treated
as number of the target data type. The actual size of the source and the
target in bytes must be equal, or an error will be given.
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 should be done be nesting the CAST functions so that the
numeric value is first converted to a character string, and then converted to
the date/time data type.
Converting a date/time data type to a character type with cast is equivalent to
using the TO_CHAR date/time function. All the same rules apply.
Converting a date/time data type to an INTEGER is equivalent to using the
TO_INTEGER date/time function. This function converts date/time column value
into an INTEGER value which represents a portion of the date/time column. If
the source data type of CAST is date/time data type, and the target data type
is INTEGER, all rules for TO_INTEGER to convert date/time into INTEGER will be
applied. The <formatspec> must be used to specify a single component of the
data/time data type (i.e. HH, MM, SS, DAYS, etc.).
Converting a date/time data type to other numeric types is also allowed using
CAST. In this case, the date/time data type is first converted to an
INTEGER applying all the TO_INTEGER rules, then is converted from INTEGER to
the target data type.
Examples
--------
You may notice that in many of the queries listed, none of the columns of
the tables are being referenced. Normally, you would have column references
in the select list, but in this case constants are used instead of columns
because it is easier to show what the input data looks like.
1. Convert date string of different format to default format.
select cast (to_date ('951023', 'YYMMDD'), char (10)) from dummy_tab;
----------
(EXPR)
----------
1995-10-23
2. Do the same thing again except use CAST instead of TO_DATE.
select cast (cast ('951023', date, 'YYMMDD'), char (10))from dummy_tab;
----------
(EXPR)
----------
1995-10-23
3. Take day of year and convert it to character.
c_date is a DATE column.
select cast (to_integer (c_date, 'DDD'), char (10)) from cast_tbl;
----------
(EXPR)
----------
241
241
293
4. Return the ASCII value of an 'A'.
select cast (0x00 || cast ('A', binary(1)), smallint) from dummy_tab;
------
(EXPR)
------
65
5. Take mininum of short int column and convert to decimal(7,2).
c_sint is a SMALLINT column.
select cast (min (c_sint), decimal(7,2)) from cast_tbl;
---------
(EXPR)
---------
-32768.00
6. Return the string length of a string in decimal.
select cast (string_length ('gimme this strings length'), dec (10,2))
from dummy_tab;
------------------
(EXPR)
------------------
25.00
7. Pull out number from inside string and convert it to decimal.
select cast (substring ('pull out 12.3 from this string', 10, 4),
decimal(7,2)) from dummy_tab;
----------
(EXPR)
----------
12.30
8. Convert an arithmetic expression to character.
select cast (3+4, char) from dummy_tab;
------
(EXPR)
------
7
9. Convert date to date string.
c_date is a DATE column.
select cast (c_date, char(50), 'MM/DD/YY') from cast_tbl order by 1;
--------------------------------------------------
C_DATE
--------------------------------------------------
08/29/95
08/29/95
10/20/95
10. Return number of seconds in interval as integer.
c_interval is an INTERVAL column.
select cast (c_interval, integer, 'SECONDS') from cast_tbl order by 1;
-----------
(EXPR)
-----------
15054
86399
86399
11. Return number of seconds in interval as float.
c_interval is an INTERVAL column.
Note: ISQL will display a float as a decimal if possible.
select cast (c_interval, float, 'SECONDS') from cast_tbl order by 1;
-----------------
(EXPR)
-----------------
15054.00
86399.00
86399.00
12. Convert an integer offset number of days to a date.
dateoff is a INTEGER column.
Values in dateoff are: 1, 60, and 2000.
Note: This example is somewhat complex because the TO_INTERVAL function
requires a 7 character string of digits with leading zeros for the
'DAYS' format.
select to_date('1971-01-01')+
to_interval(substring(cast(10000000+dateoff,char(8)),2,7),'DAYS')
from datetab;
----------
(EXPR)
----------
1971-01-02
1971-03-02
1976-06-23
|
