Oracle中的单行函数
Oracle中的单行函数
1 字符函数
UPPER()--将字符串转换为大写
SELECT UPPER('abc') FROM dual;
LOWER()-将字符串转换为小写
SELECT LOWER('ABC') FROM dual;
INITCAP()-首字母大写,其它字母小写.
SELECT INITCAP('hello') FROM dual;
REPLACE()-将指定的字符/字符串替换
SELECT REPLACE('abcd efg','cd','XXX') FROM dual;
LENGTH()-获取字符串的长度
SELECT LENGTH('hello world') FROM dual;
SUBSTR()字符串截取
SELECT SUBSTR('abcdef',0,3) FROM dual;
字符串截取(Oracle中下标都是从1开始，如果指定为0则也会默认为1)
l 从指定位置截取到结尾
l 截取部分的字符串
截取前三位:
SELECT SUBSTR(‘abcdefg’,0,3) FROM dual;
截取后三位:
SELECT SUBSTR(‘abcdefg’,LENGTH(‘abcdefg’)-2) FROM dual;
SELECT SUBSTR(‘abcdefg’,-3) FROM dual;
ASCII()将字符转换为ASCII码
SELECT ASCII(‘A’) FROM dual;
CHR()将ASCII码转换为字符
SELECT CHR(100) FROM dual;
LTRIM()/RTRIM()/TRIM去掉左/右/所有空格
SELECT LTRIM(' AAA ') FROM dual;
SELECT RTRIM(' AAA ') FROM dual;
SELECT TRIM(' AAA ') FROM dual;
LPAD()/RPAD()字符串填充
SELECT LPAD('abc',10,'*') FROM dual;
SELECT RPAD('abc',10,'*') FROM dual;
SELECT RPAD(LPAD('abc',5,'*'),10,'*') FROM dual;
INSTR()字符串查找
SELECT INSTR('Hello Wold','llo') FROM dual;
CONCAT()函数 将第二个字符串附加到第一个字符串上
SELECT CONCAT('Hello',' World') FROM dual;
2 数字函数
ROUND()四舍五入函数
不保留小数:
SELECT ROUND(759.9965) FROM dual;
保留两位小数:
SELECT ROUND(759.9965,3) FROM dual;
处理整数进位:
SELECT ROUND(759.9965,-2) FROM dual;
TRUNC() 直接截取不四舍五入
SELECT TRUNC(759.9965,2) FROM dual;
SELECT TRUNC(759.9965,-2) FROM dual;
MOD() 取模(余数)函数
SELECT MOD(10,3) FROM dual;
SELECT MOD(10.5,3) FROM dual;
3 日期函数
SYSDATE：取得当前日期
修改时间格式:
ALTER SESSION SET NLS_DATE_FORMAT = ‘yyyy-mm-dd hh24:mm:ss’
日期的操作:
三天之后的日期:
SELECT SYSDATE + 3 FROM dual;
三天之前的日期:
SELECT SYSDATE -3 FROM dual;
日期操作函数:
ADD_MONTHS()
三月之后的日期:
SELECT ADD_MONTHS(SYSDATE,3) FROM dual;
三月之前的日期:
SELECT ADD_MONTHS(SYSDATE,-3) FROM dual;
MONTHS_BETWEEN()
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,TO_DATE('1987-10-18','yyyy-mm-dd'))/12) AS HIRE FROM dual;
EXTRACT()
SELECT EXTRACT(DAY FROM DATE '2016-07-25') FROM DUAL;
LAST_DAY(x)返回包含X的月的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
4 转换函数
日期格式化标志
TO_CHAR() 将日期或者数字转化为指定格式的字符串
将日期转换为字符串:
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') FROM DUAL;
将数字转化为字符串:
SELECT
TO_CHAR(1562456845625.4512659, '999,999,999,999,999,999.9999999') AS FORMAT_A,
TO_CHAR(1562456845625.4512659, '000,000,000,000,000,000.0000000') AS FORMAT_B
FROM DUAL;
TO_DATE()-将字符串变为日期形式
SELECT TO_DATE('1979/09/19','YYYY/MM/DD') FROM DUAL;
TO_NUMBER()-将字符串变为数字
SELECT TO_NUMBER('23') * TO_NUMBER('55') AS RESULT FROM DUAL;
5 通用函数
NVL(x,value) 如果x为空，则返回value，否则返回x
SELECT NVL(NULL,'ABC') FROM DUAL;
SELECT NVL('HELLO','ABC') FROM DUAL;
NVL2(x,value1,value2) x非空则返回value1否则返回value2
SELECT NVL2(NULL,'ABC','DEF') FROM DUAL;
SELECT NVL2('F','ABC','DEF') FROM DUAL;
NULLIF()
SELECT NULLIF(1，1) FROM DUAL;
SELECT NULLIF(7，2) FROM DUAL;
DECODE()-类似于JAVA中的switc-case语句
找到:
|
SELECT DECODE(65， 60,'BAD', 65，'GOOD', 90，'EXCELLENT' ) FROM DUAL; |
找不到:
|
SELECT DECODE(30， 60,'BAD', 65，'GOOD', 90，'EXCELLENT' ) FROM DUAL; |
找不到时可以设置默认值:
|
SELECT DECODE(30， 60,'BAD', 65，'GOOD', 90，'EXCELLENT', 'UNKNOWN' ) FROM DUAL; |
CASE-WHEN-THEN: -类似于JAVA中的switch语句
值找到:
|
SELECT CASE 65 WHEN 60 THEN 'BAD' WHEN 65 THEN 'GOOD' WHEN 90 THEN 'EXCELLENT' ELSE 'UNKNOWN' END FROM DUAL; |
值找不到:
|
SELECT CASE 99 WHEN 60 THEN 'BAD' WHEN 65 THEN 'GOOD' WHEN 90 THEN 'EXCELLENT' ELSE 'UNKNOWN' END FROM DUAL; |
COALESCE()- 依次判断参数是否为空,直到第一个非空值出现
|
SELECT COALESCE(NULL,33，NULL) FROM DUAL; |
|
SELECT COALESCE(NULL,NULL，99) FROM DUAL; |
|
SELECT COALESCE(NULL,NULL，NULL) FROM DUAL; |
