SQL中函数TRUNC使用与对比 SUBSTRING(),SUBSTR(),round(),replace()等函数

大大源码 • 2023年4月20日 pm11:11 • 未分类

SQL中函数TRUNC使用与对比

TRUNC()函数
- 数字
- 日期
- 对比

TRUNC()函数

trunc：截断之意，函数trunc(numberOrDate, rule)用途有截取日期和截取数字，截取规则不会四舍五入。

数字

TRUNC(n1, n2)函数将n1截断为n2位小数。如果省略n2，则n1被截断为0位。n2可以为负，以截断小数点左边的n2位数字（使其为零）。
此函数将任何数字数据类型或任何可以隐式转换为数字数据类型的非数字数据类型作为参数。如果忽略n2，则函数返回与参数的数字数据类型相同的数据类型。如果包含n2，则函数返回NUMBER。
-- TRUNC(n1, n2) n1可以是FLOAT类型,n2为空或整数
-- 情况一: n2省略,默认截取0位 同TRUNC(18, 0)
SELECT TRUNC(18) FROM DUAL;        --18
-- 情况二: n2小于0
SELECT TRUNC(18.88, -1) FROM DUAL; --10
SELECT TRUNC(18.88, -2) FROM DUAL; --0
SELECT TRUNC(18.88, -3) FROM DUAL; --0
-- 情况三: n2大于0
SELECT TRUNC(18.88, 1) FROM DUAL; --18.8
SELECT TRUNC(18.88, 2) FROM DUAL; --18.88
SELECT TRUNC(18.88, 3) FROM DUAL; --18.88

日期

SELECT TRUNC(sysdate) FROM DUAL;  --2022-11-19  今天的日期为2022-11-19
SELECT TRUNC(sysdate, 'mm') FROM DUAL;  --2022-11-01    返回当月第一天.
SELECT TRUNC(sysdate,'yy') FROM DUAL;  --2022-1-1       返回当年第一天
SELECT TRUNC(sysdate,'dd') FROM DUAL;  --2022-11-19   返回当前年月日
SELECT TRUNC(sysdate,'yyyy') FROM DUAL;  --2022-01-01   返回当年第一天
SELECT TRUNC(sysdate,'d') FROM DUAL;  --2022-11-13 (星期天)返回当前星期的第一天
SELECT TRUNC(sysdate, 'hh') FROM DUAL;   --2022-11-19 17:00:00   当前时间为14:41
SELECT TRUNC(sysdate, 'mi') FROM DUAL; --2022-11-19 17:28:00   TRUNC()函数没有秒的精确
说明：TRUNC(n1, n2)函数,n1参数必填,当前n1参数为时间，n2参数可以省略，默认为年月日参数，n2参数可为 mm, yy, yyyy, d, hh, mi.大小写可以忽略

对比

### 截断函数 部分不适用于ORACLE
1. left（name,4）截取左边的4个字符
2. right（name,2）截取右边的2个字符
3. SUBSTRING(name,5,3) 截取name这个字段 从第五个字符开始 只截取之后的3个字符
4. SUBSTRING(name,3) 截取name这个字段 从第三个字符开始，之后的所有个字符
5. SUBSTRING(name, -4) 截取name这个字段的第 4 个字符位置（倒数）开始取，直到结束
6. SUBSTRING(name, -4，2) 截取name这个字段的第 4 个字符位置（倒数）开始取，只截取之后的2个字符
7. substring_index('www.baidu.com', '.', 2) 截取第二个 '.' 之前的所有字符
8. substring_index('www.baidu.com', '.', -2) 截取第二个 '.' （倒数）之后的所有字符
9. SUBSTR(name, 1, CHAR_LENGTH(name)-3) 截取name字段，取除name字段后三位的所有字符

### round函数也是对数字进行截取操作的，但与trunc不同的时，round函数对截取的数字进行四舍五入运算。
round(num,n2) n2为整数；n2 为正整数的时候，从小数点右边到对应的n2位上看后一位进行四舍五入，n2为负整数的时候从小数点左边开始的前一位进行四舍五入，n2位也可以为计算式；当n2为0的时候，以小数点后一位进行四舍五入。
SELECT round(3.14159265, 3) FROM DUAL; -- 3.142
SELECT round(3.14159265, 5) FROM DUAL; -- 3.14159
SELECT round(3, 3) FROM DUAL; -- 3
SELECT round(3, -3) FROM DUAL; -- 0
SELECT round(163.14159265, -1) FROM DUAL; -- 160
SELECT round(163.14159265, -2) FROM DUAL; -- 200
SELECT round(163.14159265, 0) FROM DUAL; -- 163
SELECT round(163.54159265, 0) FROM DUAL; -- 164
SELECT round(163.14159265, 5-3) FROM DUAL; -- 163.14

### replace函数用于查找指定字符串替换为新的字符串
replace(Str, oldStr, newStr),Str 为当前已知需要替换的字符串，oldStr 为当前已知字符串中需要替换的字符串部分， newStr为需要将需要替换部分的字符串替换为的新的字符串，注意需要区分大小写，语法可以嵌套使用
语法：REPLACE('字符串','搜索值','替换值');  搜索值需要注意大小写
select replace('hui_guo_yunnan.qujing', 'yunnan', 'YN') from dual; -- hui_guo_YN.qujing
select replace('hui_guo_YUNNAM.qujing', 'yunnan', 'YN') from dual; -- hui_guo_YUNNAM.qujing