oracle数据库内置函数——字符函数 日期函数的用法实例及练习

字符函数

 --cancat  连接字符串
 select concat('hello','oracele') from dual;
 或者
 select 'hello'||'oracele' from dual;
 --lpad() 左填充
 select lpad('abc',5,'*') from dual;
 --rpad() 右填充
 select rpad('abc',5,'*') from dual;
 --lower() 小写字母
 select lower(ename) from emp;
 --upper() 大写字母
 select upper(ename) from emp;
 --initcap() 首字母大写，其余小写
 select initcap(ename) from emp;
 --length() 字符串的长度
 select length(ename) from emp;
 --substr(字符串,开始位置,长度) 截取字符串
 select sunstr(ename,0,2) from emp;
 --instr(字符串,字符) 查看字符是否在字符串中存在,不存在返回0;存在则返回字符所在的的位置
 select instr('abc','a') from dual;
 --TRIM(字符 FROM 字符串)  去掉字符串首尾的字符
 select ename,trim('S' from ename) from emp;
 --to_char() 将其他类型转成字符类型
 select  to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
 --查看是否有人今天过生日
 select * from emp where to_char(hiredate,'mmdd')=to_char(sysdate,'mmdd');
 --to_date() 把其他类型转换为日期类型
 update emp set hiredate=to_date('2009-10-1','yyyy-mm-dd') where empno=9000;
 --replace(字符串,字符串1,字符串2)  将字符串中的字符1替换成字符2;
 select replace(ename,'S','*') from emp;
 --translate(字符串,字符串1,字符串2)  按位逐个替换
 select translate(ename,'abc','123') ename from emp;
 --ASCII(char) 求字符的ascii码 
 select ascii('A') from dual;

日期函数

--ADD_MONTHS(日期,数字) 在已有的日期上加一定的月份;
select add_months(sysdate,1),sysdate from dual;  
--把天数加一
select sysdate,sysdate+1 from dual;
--用系统时间减去一个日期
select sysdate-to_date('2018-7-8','yyyy-mm-dd') from dual;
--LAST_DAY(日期) 求出该日期的最后一天.
select last_day(sysdate) from dual;
--MONTHS_BETWEEN(日期1，日期2) 求出两个月之间的天数（注意返回的天数为小数)
select months_between(sysdate,'8-7月-2020') from dual;
--NEXT_DAY(d,char) 返回d指定的日期之后并满足char指定条件的第一个日期 
select next_day(sysdate,'星期一') from dual;
--NVL(字符串,替换字符) 如果字符串为空则替换，否则不替换 
select ename,nvl(comm,0) from emp;
--如果不为空，就显示第一个数据，为空时显示第二个数据
select ename,nvl2(comm,0,100) from emp;

练习

1. 有一批数据
   35-106
   2-109
   3-55
   3-106
   3-3
   108-27
   12-77
   35-55
   把它们左边的数字按照升序排列，如果左边相同，按照右边升序排列

create table listnum
           (
                list varchar2(20) not null
           )      
               
insert into listnum values(trunc(dbms_random.value(0,200),0)||'-'|| trunc(dbms_random.value(0,200),0));  
select * from listnum order by to_number(substr(list,1,instr(list,'-')-1)),to_number(substr(list,instr(list,'-')+1,length(list)-instr(list,'-')));

2. 用户表
   用户名 密码
   zhangsan 1230410235123k51o23i41203041i2
   lisi 56234y234eh566uyi00rts13twet
   wangwu 1230410235123k51o23i41203041i2

把密码中的i改成1，o改成0

 create table userinfo
           (
               username varchar2(50) not null,
               password varchar2(50) not null
           )
  insert into userinfo values(dbms_random.string('x',30),dbms_random.string('x',30));
  update userinfo set password = translate(password,'iIoO','1100');

3.学生表
学号 姓名 性别 出生日期
查询20天之内即将过生日的学生

create table student
               (
                    stuname varchar2(40) not null,
                    borndate date not null
               )
select * from student where  to_date(to_char(borndate,'mmdd'),'mmdd')  between sysdate and sysdate+20;