PLSQL 数值函数和字符函数

--查询员工编号-经理编号的绝对值
select abs(empno-mgr)
from emp

--查询员工编号,部门编号,员工编号除以部门编号的余数
select empno,deptno,mod(empno,deptno)
from emp

--把1314.520向上取整后四舍五入保留到整百
select round(ceil(1314.520),-2)
from dual

--查询员工编号减去他的工资的结果和截断到整数后的结果
select  empno-sal,trunc(empno-sal)
from emp

二:字符函数        str:string的缩写,字符串
1.upper(str)     转大写            返回值:字符型
2.lower(str)     转小写            返回值:字符型
3.initcap(str)   首字母大写        返回值:字符型

select upper('apple'),upper('Apple'),lower('APPLE'),initcap('apple')
from dual

select initcap('i我love爱apple平')
from dual

oracle怎么界定一个单词:从连续的英文字母开始,到第一个非英文字母结束

--查询员工姓名转为首字母大写,职位转为小写
select initcap(ename),lower(job)
from emp

4.length(str)      求字符串的字符长度
5.lengthb(str)     求字符串的字节长度

select length('我爱你中国'),lengthb('我爱你中国')
from dual

一个英文字母或者符号占一个字符长度或者一个字节长度
一个中文汉子或者符号占一个字符长度或者两个字节长度

--查询员工姓名,员工姓名的长度
select ename,length(ename)
from emp

6.trim(str)              去除两端空格
7.ltrim(str1[,str2])     去除左端字符
8.rtrim(str1[,str2])     去除右端字符

select trim('   abc    ')
from dual

ltrim:在str1的左端去除str2,str2不写默认空
rtrim:在str1的右端去除str2,str2不写默认空

只会在str1的左端或者右端开始找匹配str2的字符,当第一不满足匹配退出,一个一个匹配

select ltrim('aabbccdd','a'),rtrim('aabbccdd','d'),ltrim('ababccdd','a')
from dual

select ltrim('aabbccdd','ab')
from dual

--查询员工编号,去掉左端的7,再去掉左端的8 的员工编号
select empno,ltrim(ltrim(empno,7),8)
from emp

9.lpad(str1,数,str2)      左端填充   
10.rpad(str1,数,str2)     右端填充

在str1的左端或右端填充str2到数位     (数位指的是字节长度)

--在abc的左端填充两颗*
select lpad('abc',5,'*')
from dual

--在abc的左端填充两颗星
select lpad('abc',7,'星')
from dual

--在abc的左端填充两颗*,右端填充两颗星
select '**'||'abc'||'星星'
from dual

select rpad(lpad('abc',5,'*'),9,'星')
from dual

--员工姓名的左右两边各填充两颗*
select '**'||ename||'**'
from emp

select rpad(lpad(ename,lengthb(ename)+2,'*'),lengthb(ename)+2+2,'*')
from emp

11.instr(str1,str2[,数1[,数2]])       查找字符串出现的位置   
str1:原字符串
str2:要查找的字符串
数1:从第几位开始找,不写默认为1
如果数1为负数,表示从倒数第几位开始找,从后往前找,从前往后第几位
数2:第几次出现的位置,不写默认为1

在str1中找str2,从第数1位开始找,找第数2次出现的位置,如果没找到则返回0

select instr('helloworld','o'),instr('helloworld','o',1,1),
       instr('helloworld','o',1,2),instr('helloworld','o',-1,1),
       instr('helloworld','o',-5,1),instr('hellowrold','o',1,3)
from dual

select instr('helloworld','o')      第1位开始第1次出现   
      ,instr('helloworld','o',6)    第6位开始第1次出现
      ,instr('helloworld','o',1,2)  第1位开始第2次出现
      ,instr('helloworld','o',-1)   倒数1位开始第1次出现
      ,instr('helloworld','o',-5)   倒数5位开始第1次出现
      ,instr('helloworld','o',-1,2) 倒数1位开始第2次出现
from dual

--查找helloworld中o第一次和第二次出现的位置
select instr('helloworld','o',1,1),instr('helloworld','o',1,2)
from dual

--查找helloworld中l倒数第二次和倒数第三次出现的位置
select instr('hellowrold','l',-1,2),
       instr('helloworld','l',-1,3)
from dual

--查找员工姓名和员工姓名中A第一次和第二次出现的位置(不包含A的不显示)
select ename,instr(ename,'A',1,1),instr(ename,'A',1,2)
from emp
where ename like '%A%'

12.substr(str,数1[,数2])    截取字符串
str:原字符串
数1:从第数1位开始截取,包括自身,如果数1为负数,表示从倒数第几位开始截取,从前往后截取
数2:截取数2位字符,如果数2不写默认截取到最后

select substr('helloworld',5,3),substr('helloworld',5),
       substr('helloworld',-6,3)
from dual

--从helloworld中截取owo,截取后三位,截取第3-5位,截取出hrld
select substr('helloworld',5,3),substr('helloworld',-3),
       substr('helloworld',3,3),substr('helloworld',1,1)||substr('helloworld',-3)
from dual

--截取员工姓名,从字母A开始截,截取到最后
select ename,substr(ename,instr(ename,'A',1,1))
from emp
where ename like '%A%'

13.replace(str1,str2[,str3])   整体替换
str1:原字符串
str2:要替换的字符串
str3:替换成的字符串,不写默认替换为空

将str1中的str2整体替换成str3

select replace('helloworld','o','!')
from dual

--把helloworld中的l替换成*,o替换成#
select replace(replace('helloworld','l','*'),'o','#')
from dual

--把员工编号中的8替换成7,然后所有的7替换成9,去掉左端的9,
--显示替换前和替换后的员工编号
select empno,ltrim(replace(replace(empno,8,7),7,9),9)
from  emp

14.translate(str1,str2,str3)    逐一替换
str1:原字符串
str2:要替换的字符串
str3:替换成的字符串

将str1中的str2逐一替换成str3,str2的第一位替换成str3的第一位,str2的第二位替换为str3的第二位..

--把helloworld中的l替换成*,o替换成#
select translate('helloworld','lo','*#')
from dual

--第一种情况:str2的个数小于str3的个数
--所有str3多出的位数无效
select translate('helloworld','lo','*#!')
from dual

--第二种情况:str2的个数大于str3的个数
--str2多出的位数会被替换为空
select translate('helloworld','low','*#')
from dual

--第三种情况:str3为空
--结果一定为空
select translate('helloworld','lo','')
from dual

注意事项:多次替换只有第一次生效
select translate('helloworld','ll','*#')
from dual

--如果想要将字符串中的某些字符替换为空怎么处理?

--加星大法

--将helloworld中的l和o替换成空
select translate('helloworld','*lo','*')
from dual

--把helloworld中的abcde替换成空
select translate('helloworld','*abcde','*')
from dual

--把员工姓名中的AMS三个字符替换成空,显示替换前和替换后的员工姓名
select ename,translate(ename,'*AMS','*')
from emp

--把员工姓名中和职位重复的字母替换成*
select ename,job,translate(ename,job,'**********')
from emp

--查找ename和job包含相同字母的员工姓名和工作
select ename,job
from emp
where ename != translate(ename,job,'**********')

select ename,job
from emp
where ename != translate(ename,'*'||job,'*')

回顾
函数
┌系统函数┌单行函数┌数值函数
│        │        ├字符函数
│        │        ├日期函数
│        │        ├转换函数
│        │        └通用函数
│        ├聚合函数
│        └分析函数
└用户自定义函数

数值函数┌abs(数)                      绝对值                     返回值:数值型
        ├mod(数1,数2)                 取余                       返回值:数值型
        ├ceil(数)                     向上取整                   返回值:数值型
        ├floor(数)                    向下取整                   返回值:数值型
        ├round(数1[,数2])             四舍五入                   返回值:数值型
        ├trunc(数1[,数2])             截断                       返回值:数值型
        ├sign(数)                     判断正负零                 返回值:数值型
        └power(数1,数2)               次方和开方                 返回值:数值型
        
字符函数┌upper(str)                   转大写                     返回值:字符型
        ├lower(str)                   转小写                     返回值:字符型
        ├initcap(str)                 首字母转大写               返回值:字符型
        ├length(str)                  字符长度                   返回值:数值型
        ├lengthb(str)                 字节长度                   返回值:数值型
        ├trim(str)                    去除两端空格               返回值:字符型
        ├ltrim(str1[,str2])           str1左端去除str2           返回值:字符型
        ├rtrim(str1[,str2])           str1右端去除str2           返回值:字符型
        ├lpad(str1,数,str2)           在str1的左端填充str2到数位 返回值:字符型
        ├rpad(str1,数,str2)           在str1的右端填充str2到数位 返回值:字符型
        ├instr(str1,str2[,数1[,数2]]) 查找字符串                 返回值:数值型
        ├substr(str1,数1[,数2])       截取字符串                 返回值:字符型
        ├replace(str1,str2[,str3])    整体替换                   返回值:字符型
        ├translate(str1,str2,str3)    逐一替换                   返回值:字符型
        ├..