CAST函数简介

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(9999999 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(9999999 AS BIGINT) ; -- 9999999

-- 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 '123456' AS STRING) ; -- 123456
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