数据仓库服务 GaussDB(DWS)-FORMAT和CAST

时间：2025-02-12 15:05:07

FORMAT和CAST

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;SELECTCAST(CAST( Agt_Num AS INT FORMAT 'Z(17)' ) AS CHAR( 5 ) )FROMC03_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         (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(  ( price11 ) ,'99990' ) AS CHAR( 10 ) ) AS price11     FROM          product_t;

新增以下 GaussDB (DWS)函数来转换为INTEGER：

       
            CREATE OR REPLACE FUNCTION /*  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 INTEGERAS  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;