CAST 是一个复杂函数,它将一个或多个值从一种数据类型转换成另一种数据类型。
CAST 将一个或多个值从一种数据类型转换成另一种数据类型。例如,您可以使用 CAST 处理一般 XML 消息。XML 消息中的所有字段都有字符值,因此,例如,如果要对字段执行算术计算或日期/时间比较,则可以使用 CAST 将字段的字符串值转换为适当类型的值。
并不是所有转换都受支持;有关支持的转换列表,请参阅支持的类型强制转换。
CAST 返回它的第一个参数(source_expression),返回值可以包含多个值,数据类型由它的第二个参数(DataType)指定。在所有情况下,只要源表达式是 NULL,结果便为 NULL。如果源表达式的求值结果与目标数据类型不兼容,或者如果源表达式格式有误,则生成运行时错误。
CCSID 子句仅用于字符串数据类型之间的转换。它允许指定源或目录字符串的代码页。
CCSID 表达式可以是求值结果为 INT 类型值的任何表达式。它按照 CCSID 的一般 WebSphere Message Broker 规则解释。有关有效值列表,请参阅支持的代码页。
DEFAULT 子句提供一个方法以避免 CAST 语句抛出异常,该方法提供作为最后一种解决方法的返回值。
DEFAULT expression 必须是有效的 ESQL 表达式,它返回 DataType 参数指定的数据类型,否则便抛出异常。
CCSID、ENCODING 和 FORMAT 参数不适用于 DEFAULT 表达式的结果;因此,该表达式必须是正确的 CCSID、ENCODING 和 FORMAT。
对于字符串数据类型和数字或日期时间数据类型之间的转换,您可以提供可选的 FORMAT 表达式。对于源字符串型的转换,FORMAT 定义应该如何解析源字符串以满足目标数据类型的要求。对于目标字符串型的转换,它定义源表达式中的数据在目标字符串中如何格式化。
FORMAT 采用不同类型的表达式进行日期时间和数字转换。但不管是转换成字符串还是从字符串转换成其他数据类型,都可以使用同一个 FORMAT 表达式。
有关数字类型之间相互转换的更多信息,请参阅将数字格式化并解析为字符串。有关日期时间数据类型之间相互转换的更多信息,请参阅将 dateTime 格式化和解析为字符串。
FORMAT 表达式与许多其他产品(如 ICU 和 Microsoft Excel)中使用的 FORMAT 表达式功能相当。
DECLARE source DECIMAL 31415.92653589; DECLARE target CHARACTER; DECLARE pattern CHARACTER '#,##0.00'; SET target = CAST(source AS CHARACTER FORMAT pattern); -- target is now "31,415.93"
DECLARE now CHARACTER = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT "yyyyMMdd-HHmmss"); -- target is now "20041007-111656" (in this instance at least)
DECLARE source CHARACTER '01-02-03'; DECLARE target DATE; DECLARE pattern CHARACTER 'dd-MM-yy'; SET target = CAST(source AS DATE FORMAT pattern); -- target now contains Year=2003, Month=02, Day=01
DECLARE source CHARACTER '12 Jan 03, 3:45pm'; DECLARE target TIMESTAMP; DECLARE pattern CHARACTER 'dd MMM yy, h:mma'; SET target = CAST(source AS TIMESTAMP FORMAT pattern); -- target now contains Year=2003, Month=01, Day=03, Hour=15, Minute=45, Seconds=58 -- (seconds taken from CURRENT_TIME since not present in input)
DECLARE source DECIMAL -54231.122; DECLARE target CHARACTER; DECLARE pattern CHARACTER '#,##0.00;(#,##0.00)'; SET target = CAST(source AS CHARACTER FORMAT pattern); -- target is now "£(54,231.12)"
DECLARE source CHARACTER '16:18:30'; DECLARE target TIME; DECLARE pattern CHARACTER 'hh:mm:ss'; SET target = CAST(source AS TIME FORMAT pattern); -- target now contains Hour=16, Minute=18, Seconds=30
CAST(7, 6, 5 AS DATE); CAST(7.4e0, 6.5e0, 5.6e0 AS DATE); CAST(7.6, 6.51, 5.4 AS DATE);
CAST(9, 8, 7 AS TIME); CAST(9.4e0, 8.6e0, 7.1234567e0 AS TIME); CAST(9.6, 8.4, 7.7654321 AS TIME);
CAST(DATE '0001-02-03', TIME '04:05:06' AS TIMESTAMP); CAST(2, 3, 4, 5, 6, 7.8 AS TIMESTAMP);
CAST(DATE '0001-02-03', TIME '04:05:06' AS TIMESTAMP); CAST(2, 3, 4, 5, 6, 7.8 AS TIMESTAMP);
CAST(DATE '0002-03-04', GMTTIME '05:06:07' AS GMTTIMESTAMP); CAST(3, 4, 5, 6, 7, 8 AS GMTTIMESTAMP); CAST(3.1e0, 4.2e0, 5.3e0, 6.4e0, 7.5e0, 8.6789012e0 AS GMTTIMESTAMP); CAST(3.2, 4.3, 5.4, 6.5, 7.6, 8.7890135 AS GMTTIMESTAMP);
CAST(1234 AS INTERVAL YEAR); CAST(32, 10 AS INTERVAL YEAR TO MONTH ); CAST(33, 11 AS INTERVAL DAY TO HOUR ); CAST(34, 12 AS INTERVAL HOUR TO MINUTE); CAST(35, 13 AS INTERVAL MINUTE TO SECOND); CAST(36, 14, 10 AS INTERVAL DAY TO MINUTE); CAST(37, 15, 11 AS INTERVAL HOUR TO SECOND); CAST(38, 16, 12, 10 AS INTERVAL DAY TO SECOND);
CAST(2345.67e0 AS INTERVAL YEAR ); CAST(3456.78e1 AS INTERVAL MONTH ); CAST(4567.89e2 AS INTERVAL DAY ); CAST(5678.90e3 AS INTERVAL HOUR ); CAST(6789.01e4 AS INTERVAL MINUTE); CAST(7890.12e5 AS INTERVAL SECOND); CAST(7890.1234e0 AS INTERVAL SECOND);
CAST(2345.67 AS INTERVAL YEAR ); CAST(34567.8 AS INTERVAL MONTH ); CAST(456789 AS INTERVAL DAY ); CAST(5678900 AS INTERVAL HOUR ); CAST(67890100 AS INTERVAL MINUTE); CAST(789012000 AS INTERVAL SECOND); CAST(7890.1234 AS INTERVAL SECOND);
CAST(INTERVAL '1234' YEAR AS FLOAT); CAST(INTERVAL '2345' MONTH AS FLOAT); CAST(INTERVAL '3456' DAY AS FLOAT); CAST(INTERVAL '4567' HOUR AS FLOAT); CAST(INTERVAL '5678' MINUTE AS FLOAT); CAST(INTERVAL '6789.01' SECOND AS FLOAT);
CAST(INTERVAL '1234' YEAR AS DECIMAL); CAST(INTERVAL '2345' MONTH AS DECIMAL); CAST(INTERVAL '3456' DAY AS DECIMAL); CAST(INTERVAL '4567' HOUR AS DECIMAL); CAST(INTERVAL '5678' MINUTE AS DECIMAL); CAST(INTERVAL '6789.01' SECOND AS DECIMAL);
CAST(7, 6, 32 AS DATE DEFAULT DATE '1947-10-24');
CAST(2, 3, 4, 24, 6, 7.8 AS TIMESTAMP DEFAULT TIMESTAMP '1947-10-24 07:08:09');
BEGIN DECLARE EXIT HANDLER FOR SQLSTATE LIKE '%' BEGIN SET OutputRoot.XML.Data.Date.FromIntegersInvalidCast = 'Exception thrown'; END; DECLARE Dummy CHARACTER CAST(7, 6, 32 AS DATE); END;
BEGIN DECLARE EXIT HANDLER FOR SQLSTATE LIKE '%' BEGIN SET OutputRoot.XML.Data.Timestamp.FromIntegersInvalidCast = 'Exception thrown'; END; DECLARE Dummy CHARACTER CAST(2, 3, 4, 24, 6, 7.8 AS TIMESTAMP); END;