03数据库-函数

20200225

一、单行函数

1、字符函数

大小写控制函数：lower(),upper(),initcap()

select lower('HELLO') from dual; 把临时表中的字符HELLO转换成小写

lower(),upper(),initcap() 改变的是每个单词

select upper('欢迎welcome to 中国china') from dual;

2、字符控制函数

concat()连接函数

select concat(ename,job) from emp;

select ename || job from emp;

substr()截取函数

select sustr('hello,world',1,5) form dual; 从hello,world字符串的第一位开始截取，截取长度为5，返回hello

length() 字符长度函数

select length('hello,world') form dual; 读取字符串的长度

lpad/rpad 字符左/右填充函数

select empno,lpad(ename,10,'*') from emp; 位数不够时，执行截取功能

trim()首尾截断函数

select trim(' aa bb ') from dual; 去除首尾的空格，中间空格保留

select ltrim(' aa bb ') from dual; 去除首(左边开始）的空格(此写法只可去掉空格)，其余空格保留

trim(leading|trailing|both) 去掉前面|后面|前后

select ename,trim('A' from ename) from emp;去掉ename字符首尾中的A，什么都不写，默认为both

select trim(trailing 'a' from 'aadfaa aasda') from dual; 返回字符串'aadfaa aasd'

trim中截取的值，只能为一个字符

select ltrim('aabbccddaabb','ab') from dual; 从字符串的左边去除 a和b的字符，如果没有则返回原字符

instr()查找函数

select ename,instr(ename,'BE') from emp; 查找ename中含有字符BE的，并返回首次出现的位置

select instr('hello world','or',5) from dual;

select instr('hello world','o',1,1) from dual;返回第一次出现第一个o的位置

select instr('hello world','o',1,2) from dual;返回第一次出现第2个o的位置

select ename from emp where ename not like '%R%'; 查询姓名中不带有R的员工姓名

select ename from emp where instr(ename,'R')=0; 查询姓名中不带有R的员工姓名

replace()单个字符替换

replace(值,str1,str2) 将值中的str1替换为str2

select ename,replace(ename,'A','a') from emp;将姓名中的A都替换为a

translate()多个字符替换

比replace更占用空间

select ename,translate(ename,'AS','as') from emp; 将姓名中的A替换为a，S替换为s

select ename,translate(ename,'AS','a') from emp; 将姓名中的A替换为a，S去掉

周杰伦→**伦，把最后一个字前面的字都提取出来，然后用*去替换掉

select translate('周杰伦',substr('周杰伦',1,length('周杰伦')-1),'********') from dual; //**伦

select translate('成龙',substr('成龙',1,length('成龙')-1),'********') from dual; //**龙

select translate('布莱恩特',substr('布莱恩特',1,length('布莱恩特')-1),'********') from dual; //***特

3、数字函数

round()四舍五入函数、trunc()截断函数、mod()求余函数

round(45.926,2)=45.93 round(45.926,-1)=50

trunc(45.926,2)=45.92 trunc(45.926,-1)=40

mod(10,3)=1（10/3=3......1）

4、日期函数

sysdate返回当前日期

日期-数字=日期日期+数字=日期日期-日期=数字（间隔天数）

months_between返回两个日期间隔的月数

select months_between(sysdate,hiredate) from emp查询每个员工入职的月数

add_months(sysdate,2) 返回当前日期+2月后的日期

next_day（sysdate,'星期一'）返回当前日期后的下个星期一的日期，星期几怎么书写需要视当前的字符编码集，中文则输入星期几，英文则输入monday等。

last_day(sysdate) 返回当前日期所在月份的最后一天

select last_day('01-2月-2008'),last_day(sysdate) from dual; //29-2月 -08 29-2月 -20

5、转换函数

to_number()把其他类型转换成数字，目前已实现自动转换不需使用函数

select 1+1,'1'+1 from dual; //2 2

to_date()把其他类型转换成日期

select to_date('2020-02-25','yyyy-mm-dd') from dual; //25-2月 -20

to_char()把其他类型转换成字符串

select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual; //2020-02-25

把当前日期转换成年月日的中文格式：

select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual; //2020年02月25日

select to_char(sysdate,'yyyy')||'年'||to_number(to_char(sysdate,'mm'))||'月'||to_number(to_char(sysdate,'dd'))||'日' from dual; //2020年02月25日

注：字符串当中，存在自定义的字符信息的时候，需要使用双引号标记那些直接显示而不需要额外处理的字符

select to_char(sysdate,'fmyyyy"年"mm"月"dd"日"') from dual; //2020年2月25日,fm去除前导0

select to_char(sysdate,'fmyyyy-mm-dd hh24:mi:ss am') from dual; //2020-2-25 16:20:58 下午

select to_char(sysdate,'fmyyyy-mm-dd am hh24:mi') from dual; //2020-2-25下午16:20:58

美元按照默认格式，会将￥放到数额前面；其他货币是外挂上去的，会按照定义格式写

select ename,to_char(sal,'99,999$'),to_char(sal,'99,999L') from emp; //KOBE $4,000 4,000￥

select ename,to_char(sal,'99g999$'),to_char(sal,'99G999L') from emp;//KOBE $4,000 4,000￥

select add_months(sysdate,1) from dual; //当前日期+1月

select sysdate,sysdate+7 from dual; //当前日期+1周

select sysdate,sysdate+1 from dual; //当前日期+1天

select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd am hh24:mi:ss') from dual; //当前日期+1时

select to_char(sysdate+1/24/60,'yyyy-mm-dd am hh24:mi:ss') from dual; //当前日期+1分

select to_char(sysdate+1/24/60/60,'yyyy-mm-dd am hh24:mi:ss') from dual; //当前日期+1秒

select to_char(sysdate,'yyyy"年"mm"月"dd"日" "第"W"周"') from dual; //2020年02月25日第4周

select to_char(sysdate,'d'),to_char(sysdate,'day'),to_char(sysdate,'w') from dual; //d表示今天是本周的第几天（周日是第一天），day表示今天是星期几，w表示这是本月的第几周。

作者：kerwin_chyl

文章链接：https://www.jianshu.com/u/66bf7abdc5a8

本文版权归作者和简书共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。