字符处理函数
字符串大小写处理函数
lower(char)
将所给字符串char全部转换为小写
SQL> select lower('ChAr') from dual; |
upper(char)
将所给字符串char全部转换为大写
SQL> select upper('ChAr') from dual; |
initcap(char)
将字符串char中的每个单词进行首字母大写处理
SQL> select initcap('tHe cHaR') from dual; |
忽略大小写敏感性测试
创建表tb_cap01,并插入测试数据
SQL> create table tb_cap01(name varchar2(10));Table created.SQL> insert into tb_cap01 values('char');1 row created.SQL> insert into tb_cap01 values('Char');1 row created.SQL> insert into tb_cap01 values('ChAr');1 row created.SQL> insert into tb_cap01 values('other');1 row created.SQL> select * from tb_cap01;NAME----------charCharChArother |
不计字符大小写查找出char的信息
SQL> select * from tb_cap01 where lower(name) = lower('cHaR');NAME----------charCharChArSQL> select * from tb_cap01 where upper(name) = upper('cHaR');NAME----------charCharChArSQL> select * from tb_cap01 where initcap(name) = initcap('cHaR');NAME----------charCharChAr |
可以看出来使用任意大小写处理函数都可以查询出相应的信息
concat(char1,char2)
将字符char1和char2拼接起来,相当于||符号
SQL> select concat('hello','word') from dual; |
substr(char,x,[y])
从所给字符串char中取得指定范围的字符串
x:从字符串char的第几位开始,正整数位从左到右,负整数位从右到左
y:表示字符串截取的长度,如果不写几表示从x开始向右直到结束
SQL> select substr('helloword',6,2) from dual; |
length(char)
计算字符串char的长度
SQL> select length('char') from dual;LENGTH('CHAR')--------------4 |
char和varchar2字符类型会有不同
SQL> create table tb_length01(name1 char(10),name2 varchar2(10));Table created.SQL> insert into tb_length01 values('char','char');1 row created.SQL> select length(name1),length(name2) from tb_length01;LENGTH(NAME1) LENGTH(NAME2)------------- -------------10 4 |
char优点,检索效率快
varchar2优点,存储效率高
instr(char1,char2,[x],[y])
检索char2在char1中,从x处开始检索,第y次出现的位置是哪
x:若x为正整数,表示从左第x位开始检索,若x位正整数,则表示从右第x位开始检索
若x、y都省略,则表示从char1左边第一位开始向右开始检索第一次出现char2的位置
SQL> select instr('hahahehehahahehe','he',3,2) from dual; |
SQL> select instr('he','hahahehehahahehe') from dual; |
字符串处理函数
补充字符串
lpad(char1,x,char2)
表示在字符串char1中向左补充字符串char2,直到长度为x
SQL> select lpad('char1',50,'char2') from dual; |
rpad(char1,x,char2)
表示在字符串char1中向右补充字符串char2,直到长度为x
SQL> select rpad('char1',50,'char2') from dual; |
trim(leading|trailing|both char1 from char2)
leading:表示将char2左边的char1去除
trailing:表示将char2右边的char1去除
both:表示将char2两遍的char1去除(默认为both)
注意char1只能是一个字符
SQL> select trim(trailing 'hh' from 'hhhhaaaahhhhaaaahhhh') from dual;select trim(trailing 'hh' from 'hhhhaaaahhhhaaaahhhh') from dual |
因为char1用了多个字符,因此报错
leading
SQL> select trim(leading 'h' from 'hhhhaaaahhhhaaaahhhh') from dual;TRIM(LEADING'H'F----------------aaaahhhhaaaahhhh |
trailing
SQL> select trim(trailing 'h' from 'hhhhaaaahhhhaaaahhhh') from dual;TRIM(TRAILING'H'----------------hhhhaaaahhhhaaaa |
both
SQL> select trim(both 'h' from 'hhhhaaaahhhhaaaahhhh') from dual;TRIM(BOTH'H'------------aaaahhhhaaaaSQL> select trim('h' from 'hhhhaaaahhhhaaaahhhh') from dual;TRIM('H'FROM------------aaaahhhhaaaa |
当处理半角空格时可以使用ltrim,rtrim
ltrim
SQL> select ltrim(' haha haha ') from dual; |
rtrim
SQL> select rtrim(' haha haha ') from dual; |
可见不指定char1的话默认为半角空格
replace(char1,char2,char3)
表示在char1中把char2替换成char3)
SQL> select replace('hahahehehahahehe','a','*') from dual;REPLACE('HAHAHEH----------------h*h*heheh*h*hehe |
char2,和char3可以时多字符的字符串
如果像外卖一样隐藏手机号要怎么把呢?
SQL> select replace(13920782969,substr(13920782969,4,4),'****') from dual;REPLACE(139-----------139****2969 |
数字处理函数
round(number,[x])
表示求数字number的四舍五入值
x表示保留小数点后多少位,省略x默认保留整数,负数为保留小数点左边多少位
SQL> select round(35.55) no1,round(35.55,1) no2,round(35.55,-1) no3 from dual; |
trunc(number,[x])
表示把数字number在x处截断,如果省略x则默认保留整数
SQL> select trunc(35.55) no1,trunc(35.55,1) no2,trunc(35.55,-1) no3 from dual; |
mod(number1,number2)
表示取得number1除以number2得到的余数
当number2=0的时候mod(number1,number2)=number1
SQL> select mod(35,7) no1,mod(35,8) no2,mod(35,0) no3,mod(0,7) no4 from dual; |
ceil(number)
表示将数字number向上取整,所得的结果应为大于或者等于number的最小整数
SQL> select ceil(35.55) no1,ceil(-35.55) no2 from dual; |
floor(number)
表示将数字number向下取整,所得的结果应为大于或者等于number的最大整数
SQL> select floor(35.55) no1,floor(-35.55) no2 from dual; |
日期处理函数
关于日期运算日、时、分、秒等参照
日期函数
两个日期进行相差运算,得到的是两日期相差的时间
越未来的时间值越大
查询emp表中的员工入职时间
入职时间=现在时间-入职日期
SQL> select sysdate 现在日期,hiredate 入职日期,(sysdate-hiredate)/365 入职时间 from emp;现在日期 入职日期 入职时间------------------- ------------------- ----------2021-01-19 10:32:10 1980-12-17 00:00:00 40.1190112021-01-19 10:32:10 1981-02-20 00:00:00 39.94092882021-01-19 10:32:10 1981-02-22 00:00:00 39.93544932021-01-19 10:32:10 1981-04-02 00:00:00 39.82862021-01-19 10:32:10 1981-09-28 00:00:00 39.33818912021-01-19 10:32:10 1981-05-01 00:00:00 39.7491482021-01-19 10:32:10 1981-06-09 00:00:00 39.64229862021-01-19 10:32:10 1987-04-19 00:00:00 33.77928492021-01-19 10:32:10 1981-11-17 00:00:00 39.20120282021-01-19 10:32:10 1981-09-08 00:00:00 39.39298362021-01-19 10:32:10 1987-05-23 00:00:00 33.68613432021-01-19 10:32:10 1981-12-03 00:00:00 39.15736712021-01-19 10:32:10 1981-12-03 00:00:00 39.15736712021-01-19 10:32:10 1982-01-23 00:00:00 39.0176411 |
months_between(date1,date2)
表示取得两个日期之间的月数
SQL> select sysdate 现在日期,hiredate 入职日期,months_between(sysdate,hiredate) "入职时间(月)" from emp;现在日期 入职日期 入职时间(月)------------------- ------------------- ------------2021-01-19 10:54:08 1980-12-17 00:00:00 481.079172021-01-19 10:54:08 1981-02-20 00:00:00 478.9823952021-01-19 10:54:08 1981-02-22 00:00:00 478.9178792021-01-19 10:54:08 1981-04-02 00:00:00 477.5630412021-01-19 10:54:08 1981-09-28 00:00:00 471.7243312021-01-19 10:54:08 1981-05-01 00:00:00 476.5952992021-01-19 10:54:08 1981-06-09 00:00:00 475.3372342021-01-19 10:54:08 1987-04-19 00:00:00 4052021-01-19 10:54:08 1981-11-17 00:00:00 470.079172021-01-19 10:54:08 1981-09-08 00:00:00 472.3694922021-01-19 10:54:08 1987-05-23 00:00:00 403.8856212021-01-19 10:54:08 1981-12-03 00:00:00 469.5307832021-01-19 10:54:08 1981-12-03 00:00:00 469.5307832021-01-19 10:54:08 1982-01-23 00:00:00 467.885621 |
add_months(date,x)
表示在所给时间基础上加减多少个月
当x位正是是加,为负是减
x表示自然月,不用具体指定每月多少天
x可以取小数,但是会向下取整
查询下个月和上个的今天是什么日期
SQL> select sysdate 现在日期,add_months(sysdate,1) 下个月,add_months(sysdate,-1) 上个月 from dual;现在日期 下个月 上个月------------------- ------------------- -------------------2021-01-19 11:01:50 2021-02-19 11:01:50 2020-12-19 11:01:50 |
SQL> select sysdate 现在日期,add_months(sysdate,1.2) 下个月,add_months(sysdate,-1.2) 上个月 from dual;现在日期 下个月 上个月------------------- ------------------- -------------------2021-01-19 11:02:15 2021-02-19 11:02:15 2020-12-19 11:02:15 |
next_day(date,'x')
表示在所给时间基础上看下个星期几是几号
x为星期几根据数据服务器的字符集编码不用可以对应(sunday,星期日,日曜日)
SQL> !cal |
last_day(date)
表示取得所给时间当月的最后一天
SQL> select sysdate,last_day(sysdate) from dual; |
round(date[,'format'])
表示将所给时间四舍五入保留到年、月、日的第一天
当天数>16 则加上一个月
SQL> select sysdate,last_day(sysdate) from dual; |
trunc(date[,'format'])
表示将所给时间截取到年、月、日
SQL> select sysdate,trunc(sysdate,'year') from dual; |
显示转换
to_char(number|date,'x')
表示将number或者date类型的数据按照x的指定格式转换成字符串
number
将数字3.1415926525889转换成字符
SQL> select to_char(3.1415926525889,'9.9999999999999') pei from dual; |
0~9都表示显示一位数据0也可以,又一个即可
如果格式的整数部分长度无法覆盖给定数字的整数部分,则无法正常显示
如果格式的小数部分长度无法覆盖给定数字的小数部分,则会进行四舍五入
date
查看目前的显示格式
SQL> select sysdate from dual; |
将查询的结果显示成YY-MM-DD
SQL> select to_char(sysdate,'YY-MM-DD') from dual; |
日期常用格式如下
SQL> select to_char(sysdate,'ddspth') 世纪,to_char(sysdate,'year') 年,to_char(sysdate,'yyyy') 年,to_char(sysdate,'mm') 月,to_char(sysdate,'month') 月,to_char(sysdate,'mon') 月,to_char(sysdate,'dd') 日,to_char(sysdate,'day') 星期,to_char(sysdate,'dy') 星期 from dual;世纪 年 年 月 月 月 日 星期 星期 --------- ----------------- ---- -- ------- --- ---- --------- ---twentieth twenty twenty-one 2021 01 january jan 20 wednesday wed |
时间常用格式
SQL> select to_char(sysdate,'hh:mi:ss') 十二小时制,to_char(sysdate,'hh24:mi:ss') 二十四小时制 from dual;十二小时制 二十四小时制------------------------ ------------------------09:35:46 09:35:46 |
to_number(char[,'x'])
表示将字符串按照某种格式转换成数字,转换完成后可以对结果进行四则运算
将数字3.1425926转换成字符
SQL> select to_number('3.1415926') pie from dual; |
SQL> select to_number('0,003.1415926') pie from dual;select to_number('0,003.1415926') pie from dual |
如果字符中包含数字之外的字符,则会报错
to_date(char[,'x'])
表示将字符串按照某种特定格式转换成日期
先将会话的日期显示格式改成YY-MON-DD
SQL> alter session set nls_date_format='YY-MON-DD';Session altered.SQL> select sysdate from dual; |
将22-dec-22转换成日期
SQL> select to_date('22-dec-22') from dual; |
貌似是可以加上x的
但是真是这样么
年份的解析分为yy和rr方式,当给出22-dec-22的时候系统无法判断是1922还是2022
再将日期显示格式改为yyyy-mm-dd
SQL> alter session set nls_date_format='yyyy-mm-dd';Session altered. |
再看一下
SQL> select to_date('22-dec-22') from dual; |
SQL> select to_date('77-dec-22') from dual; |
SQL> select to_date('77-dec-22','yy-mm-dd') from dual; |
换成rr
SQL> select to_date('22-dec-22','rr-mm-dd') from dual; |
再改成rrrr-mm-dd
SQL> alter session set nls_date_format='rrrr-mm-dd';Session altered. |
看下结果
SQL> select to_date('22-dec-22') from dual; |
SQL> select to_date('77-dec-22') from dual; |
在用yy
SQL> select to_date('77-dec-22','yy-mm-dd') from dual; |
rr
SQL> select to_date('22-dec-22','rr-mm-dd') from dual; |
貌似发现了一些规律
在默认显示格式为yyyy的时候
如果输入22则会判断改年份为0022年
如果用yy则会显示当世纪的22年
如果用rr则会根据实际情况进行选择是当前世纪还是上世纪
在默认显示格式为rrrr的时候
除了输入22会自动变成当前世纪外其余同yyyy一样
真是这样么
现在把数据库时间改成1997年试试
SQL> alter system set fixed_date='1997-01-01';System altered.SQL> select sysdate from dual; |
再进行一轮测试
首先用yyyy格式
SQL> select to_date('22-dec-22') from dual; |
然后是rrrr
SQL> select to_date('22-dec-22') from dual; |
嗯,基本上算是验证成功了
等一下 如果系统时间是1907年呢
再试试吧
SQL> alter system set fixed_date='1907-01-01';System altered. |
yyyy
SQL> alter session set nls_date_format='yyyy-mm-dd';Session altered.SQL> select to_date('22-dec-22') from dual; |
rrrr
SQL> alter session set nls_date_format='rrrr-mm-dd';Session altered.SQL> select to_date('22-dec-22') from dual; |
可以说是验证成功了
总结一下
默认格式 | 当前年 | 指定日期 | 不指定格式 | yy | rr |
yyyy | 1997 | 22-dec-22 | 0022 | 1922 | 2022 |
1997 | 77-dec-22 | 0077 | 1977 | 1977 | |
1907 | 22-dec-22 | 0022 | 1922 | 1922 | |
1907 | 77-dec-22 | 0077 | 1977 | 1877 | |
rrrr | 1997 | 22-dec-22 | 2022 | 1922 | 2022 |
1997 | 77-dec-22 | 1977 | 1977 | 1977 | |
1907 | 22-dec-22 | 1922 | 1922 | 1922 | |
1907 | 77-dec-22 | 1988 | 1977 | 1977 |
可以看出yy是固定返回当前世纪
而rr则是按照下表进行转换
最后把数据库时间改回去
SQL> alter system set fixed_date=none;System altered.SQL> select sysdate from dual; |
隐式转换(implicit conversion)
oracle在字符串、日期和数字中会存在一些隐式转换,不需要特意指定
规则如下
from | to |
varchar2、char | number |
varchar2、char | date |
number | varchar2 |
date | varchar2 |
创建测试表tb_ic01并插入测试数据
SQL> create table tb_ic01 (info1 varchar2(30),info2 number(9,3),info3 date);Table created.SQL> desc tb_ic01;Name Null? Type----------------------------------------- -------- ---------------------------- INFO1 VARCHAR2(30) |
空值函数
nvl(value1,value2)
如果value1是空值,则返回value2
SQL> select nvl(null,'value1是空值') 空,nvl('1234','value1是空值') 非空 from dual;空 非空--------------- ------------value1是空值 1234 |
如果value1是空字符串,则也会被认为是空值
SQL> select nvl('','value1是空值') 空,nvl('1234','value1是空值') 非空 from dual;空 非空--------------- ------------value1是空值 1234 |
nvl(value1,value2,value3)
如果value1是非空值,返回value2
如果value2是空值,则返回value3
SQL> select nvl2(null,'value1是非空','value1是空值') 空值1,nvl2('1','value1是非空','value1是空值') 空值2,nvl2('','value1是非空','value1是空值') 空值3,nvl2('1','value1是非空','value1是空值') 空值4 from dual;空值1 空值2 空值3 空值4----------- ----------- ----------- ---------------value1是空值 value1是非空 value1是空值 value1是非空 |
nullif(value1,value2)
如果value1=value2,则返回null
如果value1<>value2,则返回e1
SQL> select nullif('a','a') test1,nullif(111,222) test2,nullif('','') test3,nullif('','a') test4 from dual; |
两个参数类型需要一致,否则报错
SQL> select nullif(1,'1') from dual;select nullif(1,'1') from dual |
value1不能为null,但可以是空字符串
SQL> select nullif(null,'a') from dual;select nullif(null,'a') from dual |
coalesce(value1,value2,value3,...valuen)
返回参数重的第一个非空表达式
SQL> select coalesce(null,'','a','b') from dual; |
若所n-1空值,则返回n参数的值,无论其是否为空
SQL> select coalesce(null,'',null,'') from dual; |
条件判断函数
他们可以实现if-then-else的分支效果
SQL> select * from tb_if01; |
准备并查看测试数据
case
假定条件
若JOB是CLEAR,则SAL+10
若JOB是SALESMAN,则SAL+20
若JOB是MANAGER,则SAL+30
若JOB是其他,则SAL-10
写法一
SQL> select empno,ename,job,sal default_sal, case job when'CLEAN' then sal+10 when 'SALESMAN' then sal+2 when 'MANAGER' then sal+30 else sal-10 end as new_sal from emp order by job; |
写法二
SQL> select empno,ename,job,sal default_sal, case when job='CLEAN' then sal+10 when job='SALESMAN' then sal+20 when job='MANAGER' then sal+30 else sal-10 end as new_sal from emp order by job; |
当不存在分支else的情况下sal将返回null显示在new_sal中
SQL> select empno,ename,job,sal default_sal, case job when'CLEAN' then sal+10 when 'SALESMAN' then sal+2 when 'MANAGER' then sal+30 end as sal_ne from emp order by job; select empno,ename,job,sal default_sal, case when job='CLEAN' then sal+10 when job='SALESMAN' then sal+20 when job='MANAGER' then sal+30 end as new_sal from emp order by job; |
decode(col|expr,search1,result1[,search2,result2,...,][,default])
decode是将case的书写方式进行简化
还是上述条件
换成decode写法
SQL> select empno,ename,job,sal default_sal, decode( job, 'CLEAN',sal+10, 'SALESMAN',sal+20, 'MANAGER',sal+30, sal-10) as new_sal from emp order by job; |
同样 如果default值不指定,则sal将返回null在new_sal中
SQL> select empno,ename,job,sal default_sal, decode( job, 'CLEAN',sal+10, 'SALESMAN',sal+20, 'MANAGER',sal+30) as new_sal from emp order by job; |