更新时间:2024-06-29 GMT+08:00
FORMAT和CAST
Teradata中,关键词FORMAT用于格式化列或表达式。例如,LPAD中FORMAT '9(n)'和'z(n)'分别用'0'和空格(' ')表示。
数据类型转换可使用CAST或直接数据类型([like (expression1)(CHAR(n))])进行。该功能使用CAST实现。详情参见类型转换和格式化。
输入:FORMAT和CAST
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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'
1 2 3 4 5 6 |
SELECT standard_price (FORMAT 'Z(5)9') (CHAR( 6 )) ,max_price (FORMAT 'ZZZZZ9') (CHAR( 6 )) FROM product_t ; |
输出:
1 2 3 4 5 6 |
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)'
1 2 3 4 5 |
SELECT standard_price (FORMAT 'Z(6)9.9(2)') (CHAR( 6 )) FROM product_t ; |
输出:
1 2 3 4 5 |
SELECT CAST( TO_CHAR( standard_price ,'9999990.00' ) AS CHAR( 6 ) ) AS standard_price FROM product_t ; |
输入:CAST AS INTEGER
1 2 3 4 5 |
SELECT CAST( standard_price AS INTEGER ) FROM product_t ; |
输出:
1 2 3 4 5 |
SELECT (standard_price) FROM product_t ; |
输入:CAST AS INTEGER FORMAT
1 2 3 4 5 6 7 |
SELECT CAST( price11 AS INTEGER FORMAT 'Z(4)9' ) ( CHAR( 10 ) ) FROM product_t ; |
输出:
1 2 3 4 5 |
SELECT CAST( TO_CHAR( ( price11 ) ,'99990' ) AS CHAR( 10 ) ) AS price11 FROM product_t ; |
![](https://support.huaweicloud.com/intl/zh-cn/tg-dws/public_sys-resources/note_3.0-zh-cn.png)
新增以下Gauss函数来转换为INTEGER:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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; |
父主题: 查询迁移操作符