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;