文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Teradata语法迁移> 查询迁移操作符> FORMAT和CAST

FORMAT和CAST

分享
更新时间: 2019/08/09 GMT+08:00

Teradata中,关键词FORMAT用于格式化列或表达式。例如,LPAD中FORMAT '9(n)'和'z(n)'分别用'0'和空格(' ')表示。

数据类型转换可使用CAST或直接数据类型([like (expression1)(CHAR(n))])进行。该功能使用CAST实现。详情参见类型转换和格式化

输入:FORMAT和CAST
SELECT
          CAST(TRIM( Agt_Num ) AS DECIMAL( 5 ,0 ) FORMAT '9(5)' )
     FROM
          C03_AGENT_BOND
;

SELECT
          CAST(CAST( Agt_Num AS INT FORMAT 'Z(17)' ) AS CHAR( 5 ) )
     FROM         
          C03_AGENT_BOND
;

SELECT
          CHAR(CAST( CAST( CND_VLU AS DECIMAL( 17 ,0 ) FORMAT 'Z(17)' ) AS VARCHAR( 17 ) ) )
     FROM
          C03_AGENT_BOND
;

输出

SELECT
          LPAD( CAST( TRIM( Agt_Num ) AS DECIMAL( 5 ,0 ) ) ,5 ,'0' ) AS Agt_Num
     FROM
          C03_AGENT_BOND
;

SELECT
          CAST( LPAD( CAST( Agt_Num AS INT ) ,17 ,' ' ) AS CHAR( 5 ) ) AS Agt_Num
     FROM
          C03_AGENT_BOND
;

SELECT
          LENGTH( CAST( LPAD( CAST( CND_VLU AS DECIMAL( 17 ,0 ) ) ,17 ,' ' ) AS VARCHAR( 17 ) ) ) AS CND_VLU
     FROM
          C03_AGENT_BOND
;

输入:FORMAT 'Z(n)9'

SELECT
          standard_price (FORMAT 'Z(5)9') (CHAR( 6 ))
          ,max_price (FORMAT 'ZZZZZ9') (CHAR( 6 ))
     FROM
          product_t
; 

输出

SELECT
          CAST( TO_CHAR( standard_price ,'999990' ) AS CHAR( 6 ) ) AS standard_price
          ,CAST( TO_CHAR( max_price ,'999990' ) AS CHAR( 6 ) ) AS max_price
     FROM
          product_t
; 

输入:FORMAT 'z(m)9.9(n)'

SELECT
          standard_price (FORMAT 'Z(6)9.9(2)') (CHAR( 6 ))
     FROM
          product_t
;

输出

SELECT
          CAST( TO_CHAR( standard_price ,'9999990.00' ) AS CHAR( 6 ) ) AS standard_price
     FROM
          product_t
;

输入:CAST AS INTEGER

SELECT
          CAST( standard_price AS INTEGER )
     FROM
          product_t
;

输出

SELECT
         mig_td_ext.mig_fn_castasint (standard_price)
     FROM
          product_t
;

输入:CAST AS INTEGER FORMAT

SELECT
          CAST( price11 AS INTEGER FORMAT 'Z(4)9' ) (
               CHAR( 10 )
          )
     FROM
          product_t
;

输出

SELECT
          CAST( TO_CHAR( fn_td_castasint ( price11 ) ,'99990' ) AS CHAR( 10 ) ) AS price11
     FROM
          product_t
;
说明:

新增以下高斯函数来转换INTEGER:

CREATE OR REPLACE FUNCTION mig_td_ext.mig_fn_castasint
/*  This function is used to support "CAST AS INTEGER" of Teradata.
    It should be created in the "mig_td_ext" schema.
*/
     ( i_param                            TEXT )
RETURN INTEGER
AS
  v_castasint    INTEGER;
BEGIN

   v_castasint := CASE WHEN i_param IS NULL 
                                                THEN NULL         -- if NULL value is provided as input
                                                                WHEN TRIM(i_param) IS NULL 
                                                                                THEN 0                  -- if empty string with one or more spaces is provided 
                                                                ELSE TRUNC(CAST(i_param AS NUMBER))            -- if any numeric value is provided
                                END;

RETURN v_castasint;
END;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区