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

时间：2024-12-10 11:28:48

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
;
SELECT
CAST(CAST( Agt_Num AS INT FORMAT 'Z(17)' ) AS CHAR( 5 ) )
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
         (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 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;