Oracle常用函数

Oracle常用函数

字符串函数

字符串拼接函数concat(字符串1，字符串2)

字符串检索函数instr(待检索字符串，检索子串[，起始检索位置])

字符串长度函数length(字符串)

字符串大小写转换函数，lower(),upper()

字符串去除两边空格 trim(),ltrim(),rtrim()

字符串替换函数replace()

字符串截取函数substr()

-- CONCAT('字符串1','字符串2') 拼接两个字符串
SELECT CONCAT('HELLO',' WORLD') FROM DUAL;

-- INSTR('待检索字符串','子字符串',开始查询位置) 下标从1开始，返回子字符串第一次匹配成功的起始下标，匹配失败返回0
SELECT INSTR('oWo','1',2) FROM DUAL;

-- LENGTH('字符串') 返回字符串的长度
SELECT LENGTH('Hello') FROM DUAL;

-- LOWER('字符串') 字符串全部小写， UPPER('字符串') 字符串全部大写
SELECT LOWER('Hello'),UPPER('Hello') FROM DUAL;

-- LTRIM('字符串')：去掉左边的空格， RTRIM('字符串')：去掉右边的空格， TRIM('字符串')：去掉两边的空格
SELECT LTRIM(' oolloo '), RTRIM(' oolloo '), TRIM(' oolloo ') FROM DUAL;

-- REPLACE('字符串','旧子字符串','新子字符串') 旧子字符串统一换成新子字符串
SELECT REPLACE('hello hello world','hello','Hello') FROM DUAL;

-- SUBSTR('字符串',开始下标[,长度]) 下标从1开始
SELECT SUBSTR('Hello',2,1) FROM DUAL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

数值函数

绝对值函数 abs()

向上取整函数 ceil()

向下取整函数 floor()

对数函数 log()

取模函数mod()

幂函数power()

四舍五入函数round()

平方根函数sqrt()

截断函数trunc()

-- ABS(num) 取num的绝对值
SELECT ABS(-3) FROM DUAL;

-- CEIL(num) 取>=num的最小整数
SELECT CEIL('5.4') FROM DUAL;

-- FLOOR(num) 取<=num的最大整数
SELECT FLOOR('5.4') FROM DUAL;

-- LOG(底数，对数) 结果可能不精确
SELECT LOG(2,4) FROM DUAL;

-- MOD(num,模) num除以模取余
SELECT MOD(8,3) FROM DUAL;

-- POWER(num,次幂）
SELECT POWER(2，4) FROM DUAL;

-- ROUND(num,小数位) num在第几个小数位进行四舍五入
SELECT ROUND(4.536,2) FROM DUAL;

-- SQRT(num) num的平方根
SELECT SQRT(9) FROM DUAL;

-- TRUNC(num,小数位) num在第几个小数位截断
SELECT TRUNC(4.536,2) FROM DUAL;

-- 整数位的四舍五入或者截断
SELECT TRUNC(456.3,-2) FROM DUAL; 
SELECT ROUND(456.3,-2) FROM DUAL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

日期函数

月份加减函数add_months()

日期四舍五入函数round()

日期提取函数extract()

-- ADD_MONTH(date,add_num) 返回加上指定月份的新日期
SELECT ADD_MONTHS(SYSDATE,4) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,-4) FROM DUAL;

-- LAST_DAY(d) 返回指定月份的最后一天日期
SELECT LAST_DAY(SYSDATE) FROM DUAL;

-- ROUND(date,'YEAR'/'MONTH'/'DDD'/'DAY')四舍五入时间
SELECT 
    ROUND(SYSDATE,'YEAR'), -- 上半年四舍，下半年五入
    ROUND(SYSDATE,'MONTH'), -- 上半月四舍，下半月五入，
    ROUND(SYSDATE,'DDD'), -- 上半天四舍，下半天五入，
    ROUND(SYSDATE,'DAY')  -- 上半周四舍，下半周五入
FROM 
    DUAL;

-- EXTRACT(YEAR/MONTH/DAY/HOUR/MINUTE/SECOND FROM date) 提取日期中特定部分
SELECT
    SYSDATE,
    EXTRACT(YEAR FROM SYSDATE),
    EXTRACT(MONTH FROM SYSDATE),
    EXTRACT(DAY FROM SYSDATE),
    EXTRACT(HOUR FROM SYSTIMESTAMP),
    EXTRACT(MINUTE FROM SYSTIMESTAMP),
    EXTRACT(SECOND FROM SYSTIMESTAMP)
FROM
    DUAL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

转换函数

日期或数值转字符串函数to_char()

字符串转日期函数to_date()

字符串转数值函数to_number()

-- TO_CHAR(NUM/DATE[,'格式化字符串']) 数字或者日期转化成字符串
SELECT
    TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
FROM
    DUAL;
    
SELECT
    TO_CHAR(-12345.6,'L9.99999EEEEPR') 
    -- L: 本地货币符号
    -- PR: 负数时尖括号括起来
    -- EEEE: 科学计数法
    -- 9: 指定位置显示数字
    -- .:指定位置加上小数点
FROM
    DUAL;
    
-- TO_DATE('日期字符串'[,'格式化字符串']) 字符串转换成日期
SELECT TO_DATE('1999-05-29 22:30','YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- TO_NUMBER('字符串转一个数字')
SELECT TO_NUMBER('-$12,345.67','$99,999.99') FROM DUAL; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

其他函数

空if函数NVL(),NVL2()

-- NVL(X,VALUE) X为空，显示VALUE数据
SELECT NVL(NULL,'3.1415') FROM DUAL;

-- NVL2(X,VALUE1,VALUE2) X不为空,显示VALUE1; X为空,显示VALUE2
SELECT NVL2(NULL,1,2) FROM DUAL;
1
2
3
4
5