CAST函数简介

大家好，欢迎来到IT知识分享网。

CAST函数简介

将某种数据类型的表达式,显式转换为另一种数据类型。

1- 语法

cast(<expr> as <type>) 

参数说明：

• expr：表达式，必填。
• type：目标数据类型，必填。

expr：必填。待转换数据源。 type：必填。目标数据类型。用法如下： cast(double as bigint)：将DOUBLE数据类型值转换成BIGINT数据类型。 cast(string as bigint)：在将字符串转为BIGINT数据类型时，如果字符串中是以整型表达的数字，则会直接将它们转为BIGINT类型。如果字符串中是以浮点数或指数形式表达的数字，则会先转为DOUBLE数据类型，再转为BIGINT数据类型。 cast(string as datetime)或cast(datetime as string)：会采用默认的日期格式yyyy-mm-dd hh:mi:ss。 除此之外，cast还支持基本数据类型与JSON类型之间的相互转换，所支持的类型包括：JSON/STRING/BIGINT/INT/TINYINT/SMALLINT/DOUBLE/FLOAT/BOOLEAN/SQL-TYPE。示例用法如下： cast(json as string)：将JSON表达式转换为STRING类型。JSON表达式要求为非ARRAY和OBJECT类型。 cast(string as json)：将STRING类型值转换为JSON表达式，JSON表达式的类型为STRING。注意其与json_parse和json_format的区别，json_parse只支持合法的JSON STRING转成JSON，而且可以转成JSON OBJECT ，而cast(string as json) 可以将任意STRING转成JSON STRING，JSON类型是STRING。 cast(null as json)：将NULL值转换为JSON类型。 cast(json 'null' as ...)：json 'null'和null会转换成sql null。 

2- 使用示例

-- CAST AS BOOLEAM -- 字符串:将STRING数据类型值转换成BOOLEAN数据类型，当STRING为空字符串时返回false，否则返回true。 SELECT CAST('true' AS BOOLEAN) ; -- true SELECT CAST('f' AS BOOLEAN) ; -- true SELECT CAST('false' AS BOOLEAN) ; -- true SELECT CAST('a' AS BOOLEAN) ; -- true SELECT CAST('' AS BOOLEAN) ; -- false -- int SELECT CAST(1 AS BOOLEAN) ; -- true SELECT CAST(0 AS BOOLEAN) ; -- false SELECT CAST(6 AS BOOLEAN) ; -- true SELECT CAST(-2 AS BOOLEAN) ; -- true SELECT CAST(01 AS BOOLEAN) ; -- true -- double SELECT CAST(1.6 AS BOOLEAN) ; -- true SELECT CAST(0.6 AS BOOLEAN) ; -- true SELECT CAST(0.0 AS BOOLEAN) ; -- false SELECT CAST(-2.2 AS BOOLEAN) ; -- true SELECT CAST(NULL AS BOOLEAN) ; -- NULL -- CAST AS FLOAT SELECT CAST(20230 AS FLOAT) ; -- 20230.0 SELECT CAST(2.205 AS FLOAT) ; -- 2.205 SELECT CAST(NULL AS FLOAT) ; -- NULL SELECT CAST('cas' AS FLOAT) ; -- NULL -- CAST AS INT SELECT CAST(20230 AS INT) ; -- 20230 SELECT CAST(2.205 AS INT) ; -- 2 SELECT CAST(NULL AS INT) ; -- NULL SELECT CAST('cas' AS INT) ; -- NULL -- CAST AS DECIMAL SELECT CAST(20230 AS DECIMAL(8,2)) ; -- 20230 SELECT CAST(2.205 AS DECIMAL(8,2)) ; -- 2.21 SELECT CAST(NULL AS DECIMAL(8,2)) ; -- NULL SELECT CAST('cas' AS DECIMAL(8,2)) ; -- 不支持String类型 -- CAST AS SMALLINT SELECT CAST(20230 AS SMALLINT) ; -- 20230 SELECT CAST(2.205 AS SMALLINT) ; -- 2 SELECT CAST(NULL AS SMALLINT) ; -- NULL SELECT CAST('cas' AS SMALLINT) ; -- NULL SELECT CAST( AS SMALLINT) ; -- -27009 -- CAST AS BIGINT SELECT CAST(20230 AS BIGINT) ; -- 20230 SELECT CAST(2.205 AS BIGINT) ; -- 2 SELECT CAST(NULL AS BIGINT) ; -- NULL SELECT CAST('cas' AS BIGINT) ; -- 0 SELECT CAST('123dkha' AS BIGINT); -- 123 SELECT CAST('1' AS BIGINT) ; -- 1 SELECT CAST( AS BIGINT) ; --  -- CAST AS DOUBLE SELECT CAST(20230 AS DOUBLE) ; -- 20230.0 SELECT CAST(2.205 AS DOUBLE) ; -- 2.205 SELECT CAST(NULL AS DOUBLE) ; -- NULL SELECT CAST('cas' AS DOUBLE) ; -- NULL -- CAST AS STRING SELECT CAST(20230 AS STRING) ; -- '20230' SELECT CAST(2.205 AS STRING) ; -- '2.205' SELECT CAST(NULL AS STRING) ; -- NULL SELECT CAST('cas' AS STRING) ; -- 'cas' -- CAST AS TIMESTAMP SELECT CAST(20230 AS TIMESTAMP) ; -- '20230' SELECT CAST(2.205 AS TIMESTAMP) ; -- '2.205' SELECT CAST(NULL AS TIMESTAMP) ; -- NULL SELECT CAST('cas' AS TIMESTAMP) ; -- 'cas' -- string 和 json 之间的互相转换 SELECT CAST(JSON '' AS STRING) ; --  SELECT CAST(JSON '"sdfk"' AS STRING) ; -- sdfk SELECT CAST(JSON 'true' AS STRING) ; -- TRUE SELECT CAST(JSON 'null' AS STRING) ; -- NULL SELECT CAST(JSON '' AS STRING) ; -- 报错:Parse exception - invalid JSON format: '''' -- 不支持array/object类型的JSON表达式转换为string SELECT CAST(JSON '{"cc","ss"}' AS STRING) ; -- 报错: Parse exception - invalid JSON format: ''{"cc","ss"}'' SELECT CAST('{"da":2}' AS JSON) ; -- "{\"da\":2}" SELECT CAST('222' AS JSON) ; -- "222" SELECT CAST(222 AS JSON) ; -- 222 SELECT json_type(CAST(NULL AS JSON)) ; -- NULL SELECT CAST(JSON '"2.369"' AS FLOAT) ; -- 2.369 SELECT CAST(JSON '258' AS BIGINT) ; -- 258 SELECT CAST(JSON '2.369' AS DOUBLE) ; -- 2.369 

END