sql常用函数

1.CAST

语法

类型强转：
CAST (<expression> AS <data_ type>[ length ])；cast( 列名/值 as 数据类型 )
e.g. cast(phone as varchar/integer/int)

四舍五入

--截断小数
SELECT CAST('123.447654' AS decimal(5,2)) as result from tab_nm;
RESULT

123.45
decimal(5,2)表示值总位数为5，精确到小数点后2位。

截取

SELECT CAST('123.4' AS decimal) as result from dual;
结果是一个整数值：123；

时间转换

还可以用于时间的转换：
2018-3-21 10:40:09
date
select cast(date as varchar) as date from table1;
结果如下：
date
2018-3-21 10:40:09
select cast(date as date) as date from table1;
结果如下：
date
2018-3-21
select cast(date as time) as date from table1;
结果如下：
date
10:40:09

2.substr

语法

substr(str,start,length)
start起始位置从1开始，length截取长度