Oracle-常用函数

1.to_char：将时间或数字转为字符串

to_char处理数字：TO_CHAR(number,'格式')

TO_CHAR(salary,'$99,999.99')
TO_CHAR(200,L99G999D99MI)  //¥200.00

to_char处理日期：TO_CHAR(date,'格式')

TO_CHAR(date,'yyyy-mm-dd hh24:mi:ss')

查询六月份出生的学生：

SELECT sname,birthday
FROM student
WHERE TO_CHAR(birthday,'mm') = 6

2.TO_NUMBER:TO_NUMBER函数()是Oracle中常用的类型转换函数之一，主要是将字符串转换为数值型的格式，与TO_CHAR()函数的作用正好相反。

用法：To_number（varchar2 or char,’format model’）

To_number函数中也有很多预定义的固定格式：

格式值	含义
9	代表一个数字
0	强迫0显示
$	显示美元符号
L	强制显示一个当地的货币符号
.	显示一个小数点
,	显示一个千位分隔符号

SELECT TO_NUMBER('RMB46.3560','L99999.0000') FROM dual    //46.356

注意：有的时候你会发现，使用了TO_NUMBER()函数并且语法正确，但是Oracle却报“invalid number”的错误，而你在一遍又一遍认认真真检查并确定语句无误之后大呼惊奇，以为TO_NUMBER()函数还有什么可能不知道的用法。其实这很可能是你所查询的数据出现了问题，而非SQL。使用TO_NUMBER()函数的时候，一定要确保所转换字段是可转换为数字的，比如字符串“20151008”是可以转换为数字20151008的，但是字符串“2015-10-08”不可以。如果你的字段中包含了字符串“2015-10-08”，而你还直接使用了TO_NUMBER()函数进行操作的话就会报“invalid number”的错。

3.TO_DATE：转化为时间格式

年：

格式值	含义
yy	显示值:18
yyy	显示值:018
yyyy	显示值:2018

月：

格式值	含义
mm	显示值:11
mon	显示值:11月,若是英文版,显示nov
month	显示值:11月,若是英文版,显示november

日：

格式值	含义
dd	当月第几天 显示值:02
ddd	当年第几天 显示值:02
dy	当周第几天简写 显示值:星期五,若是英文版,显示fri
day	当周第几天简写 显示值:星期五,若是英文版,显示friday

小时：

格式值	含义
hh	12小时进制 显示值:01
hh24	24小时进制 显示值:13

分：

格式值	含义
mi	60进制 显示值:06

秒：

格式值	含义
ss	60进制 显示值:06

其它：

格式值	含义
Q	季度 显示值:2
WW	当年第几周 显示值:52
W	当月第几周 显示值:2

TO_DATE('2018-08-30 08:25:45','yyyy-mm-dd hh24:mi:ss')

4.MOD：求余

MOD(n1,n2),返回n1除以n2的余数，n2=0时返回n1

SELECT MOD(43,5) FROM table  //3

5.ROUND:四舍五入

ROUND(n1,[,n2])，n2大于 0，则四舍五入到指定的小数位，n2等于 0或缺省，则四舍五入到最接近的整数，n2小于 0，则在小数点左侧进行四舍五入

ROUND(3.1415926,2)      //3.14;
ROUND(3.1415926,3)      //3.142;
SELECT ROUND(193,-2) FROM dual;      //200
SELECT ROUND(193,-1) FROM dual;      //190
SELECT ROUND(193,-3) FROM dual;      //0

6.TRUNC：截取

TRUNC(n1,[,n2])，n2大于 0，则向后截取，n2等于 0或缺省，则取整数，n2小于 0，则向前截取

SELECT TRUNC(123.458) FROM dual;    //123
SELECT TRUNC(123.458, 0) FROM dual;  //123
SELECT TRUNC(123.458, 1) FROM dual;  //123.4
SELECT TRUNC(123.458, -1) FROM dual; //120
SELECT TRUNC(123.458, -4) FROM dual;  //0
SELECT TRUNC(123.458, 4) FROM dual;  //123.458
SELECT TRUNC(123) FROM dual;  //123
SELECT TRUNC(123, 1) FROM dual;  //123
SELECT TRUNC(123, -1) FROM dual;  //120

7.TRIM：出去首位空格

TRIM：用来移除掉一个字串中的字头或字尾。删除字符串首尾的空白（可以首尾一起，也可以指定首或尾，取决于控制参数），但会保留字符串内部作为词与词之间分隔的空格

LTRIM(字串):将字串左边的空格移除。

RTRIM(字串): 将字串右边的空格移除。

TRIM(字串): 将字串首尾两端的空格移除,作用等于RTRIM和LTRIM两个函数共同的结果。

ALLTRIM(字串)：将字串左右边两边的空格移除。

SELECT RTRIM('    aaa111  bbb  ccc     ') FROM dual;

注意：字符串左右的空格真的可以全部都去掉吗？不见得。如果你的字符串两侧带有全角空格，你就会惊奇的发现，全角空格完全去不掉！包括Java中trim（）方法，对于字符串前后的全角空格也是无能为力。那么如何解决呢？

去除字符串左右 / 中指定字符（进阶用法）

去除空格仅仅是这三个函数的“默认行为”，事实上，这三个函数还可以接收其他的参数来完成在指定字符串左右 / 中去除字符。

TRIM( leading | trailing | both string1 FROM string2)：从string2中去除左侧 | 右侧 | 左右两侧（默认是both，即左右侧都去掉）的string1字符，注意，string1只能是单个字符。

SELECT TRIM(leading  '  '  FROM  '    aaa  bbb  ccc   ') FROM dual;

LTRIM(string1,string2)，RTRIM(string1,string2)：从字符串string1左侧（右侧）开始去除与string2字符集合中单个字符匹配的字符，直到在string1中遇上某个字符，该字符不在string2字符集合中。

oracle中的这两个函数中的第二个参数，可以是多个字符组成的“字符集合”，为什么说是“字符集合”而不是“字符串”（虽然严格意义上讲并没有字符集合这个概念），因为在以string2为基础对string1做字符去除的时候，string2并不是作为一个字符串那样的整体来与string1匹配的，而是单个单个字符的匹配。比如string1='abccba'，string2='abc'，如果string2作为一个整体来匹配，显然不管是用ltrim还是rtrim函数，对string1操作之后返回的结果应该是'cba'，但实际上不是，返回的是一个空串！

8.REPLACE：替换

REPLACE(str1,str2,[,str3]):str3替换str1中出现的所有str2，返回新的字符串，如果有某个参数为NULL，此函数返回NULL
该函数可以多次替换，只要str1中还有str2存在，最后都被替换成str3
若str3为空，则删除str2

REPLACE('hello world,'h','H')   //Hello world

REPLACE('abca','a')    //bc

9.SYSDATE：返回数据库所在操作系统上的时间

SELECT SYSDATE,CURRENT_DATE FROM table

10.其他

函数1	作用
upper	转大写
lower	转小写
substr	取子串
length	长度
initcap	首字母大写