CAST 和CONVERT 函数
语法
CAST(expr AS type) ,CONVERT(expr,type) ,CONVERT(expr USING
transcoding_name)
函数说明
CAST()和CONVERT()函数用于将一个类型的数值转换到另一个类型。
type 可以是下列值之一:
•
CHAR、DATE、DATETIME、DECIMAL、TIME、NUMERIC、INT、
FLOAT、DOUBLE、VARCHAR、TIMESTAMP。
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
668
•
CAST()和CONVERT(...USING...)是标准的SQL 语法。
•
CAST(str AS BINARY)等价于BINARY str。
•
CAST(expr AS CHAR)把表达式看作是默认字符集中的字符串。
•
CAST(expr AS float(M,D))、
CAST(expr AS double(M,D))中M 最大值255,
D 最大值30。
•
CAST(expr AS Float(X))指定长度,当X<24 时,按照float 处理;当
24注意
使用CAST()函数改变列类型为DATE,DATETIME 或TIME,只是标识此
列,使其变为一个指定的数据类型,而不是改变列的值。
CAST()的最终执行结果将会转化为指定的列类型。
查询时将数据使用cast 转化为varchar(0)会输出空串,使用create table as
select from 从已有表中查询非空列进行转换varchar 建新表,如果非空列转
换成varchar(0)会报错。
示例
示例1:将NOW()转换为DATE 类型。
gbase> SELECT CAST(NOW() AS DATE) FROM dual;
+---------------------+
| CAST(NOW() AS DATE) |
+---------------------+
| 2020-04-01
|
+---------------------+
1 row in set
示例2:字符串和数字类型的转换是隐式操作,用户使用时只要把字符串值当做
一个数字即可。
gbase> SELECT 1+'1' FROM dual;
+-------+
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
669
| 1+'1' |
+-------+
|
2 |
+-------+
1 row in set
示例3:CAST(str AS BINARY)等价于BINARY str。
gbase> SELECT CAST('a' AS BINARY) = 'a ' FROM dual;
+----------------------------+
| CAST('a' AS BINARY) = 'a ' |
+----------------------------+
|
0 |
+----------------------------+
1 row in set
gbase>
SELECT 'A' = 'a ';
+------------+
| 'A' = 'a ' |
+------------+
|
1 |
+------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> SELECT BINARY 'A' = 'a ' FROM dual;
+-------------------+
| BINARY 'A' = 'a ' |
+-------------------+
|
0 |
+-------------------+
1 row in set
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
670
示例4:CAST(str AS varchar(X))示例
gbase> select cast('1.2345' as varchar) as data_varchar;
+--------------+
| data_varchar |
+--------------+
| 1.2345
|
+--------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select cast('1.2345' as varchar(10)) as data_varchar;
+--------------+
| data_varchar |
+--------------+
| 1.2345
|
+--------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select cast('1.2345' as varchar(3)) as data_varchar;
+--------------+
| data_varchar |
+--------------+
| 1.2
|
+--------------+
1 row in set, 1 warning (Elapsed: 00:00:00.00)
gbase> select cast('1.2345' as varchar(0)) as data_varchar;
+--------------+
| data_varchar |
+--------------+
|
|
+--------------+
1 row in set, 1 warning (Elapsed: 00:00:00.01)
gbase> create table t3 as select cast(a as varchar) as a from t;
Query OK, 2 rows affected (Elapsed: 00:00:00.51)
gbase> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a
| varchar(11) | YES
|
| NULL
|
|
+-------+-------------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
671
gbase> create table t4 as select cast(a as varchar(0)) as a from t;
ERROR 1705 (HY000): gcluster DML error:
[192.168.146.21:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: Truncated incorrect CHAR(0) value: '1'
SQL: SELECT /*192.168.146.20_6_31_2021-01-14_15:25:42*/ /*+
TID('111') */ cast(`vcname000001.testdb.t`.`a` as char(0)) AS `a` FROM
`testdb`.`t_n1` `vcname000001.testdb.t` target into server (HOST
'192.168.146.21,192.168.146.20', PORT 5050, USER 'root', PASSWORD '',
DATABASE 'testdb', TABLE 't4_n1', COMMENT 'col_seq 0, table_host 0 0
1, scn 18, distribution 1' )