函数

/函数/

一.概念

按不同的功能用途而封装好的程序，不必花费大量的心思编写相应的程序，直接使用即可。

函数由函数名、参数组成

函数定义上分为: 系统函数 自定义函数

二.系统函数
1.字符型函数
2.日期函数
3.转换函数
4.数值函数
5.其他函数

三.字符型函数
1.CONCAT()连接函数 、||

CONCAT(STR1,STR2):将STR1和STR2拼接在一起，一次只能包含两个参数;
STR1||STR2…:将STR1和STR2拼接在一起,可以一次拼接多个数据,在其他数据库不能使用
WM_CONCAT(COLUMN):将该字段的数据拼接到一起，中间用逗号间隔

示例：
1.数据和数据拼接
SELECT CONCAT(‘67班’,‘马上放假’) FROM DUAL;
SELECT ‘67班’||‘马上放假’ FROM DUAL;

 SELECT CONCAT(CONCAT('67班','马上放假'),'哈哈哈') FROM DUAL;  
 SELECT '67班'||'马上放假'||'哈哈哈' FROM DUAL;  

2.数据和字段

 SELECT CONCAT('该员工的编号为：',EMPNO) FROM EMP; 
 SELECT '该员工的编号为：'||EMPNO FROM EMP; 

3.字段和字段

 SELECT CONCAT(ENAME,SAL) FROM EMP; 
 SELECT ENAME||EMPNO FROM EMP;
 
 SELECT WM_CONCAT(ENAME) FROM EMP;

注意：
1).CONCAT在使用时含有两个参数，若拼接多个数据需要嵌套函数
2).拼接表中的列时，不需要加引号声明

2.大写小写转换函数：

LOWER(STR1)/UPPER(STR1) 将括号内的字符转换成小写/大写
INITCAP(STR) 将字符串中的每个单词改成首字母大写其他字母小写的形式

示例：
1).将员工表中的姓名列全部小写
SELECT LOWER(ENAME) FROM EMP;
SELECT LOWER(‘asd’) FROM EMP;

  SELECT UPPER(ENAME) FROM EMP;
  SELECT UPPER('asd') FROM EMP;

2).将姓名列的字符首字母大写

  SELECT INITCAP(ENAME) FROM EMP;   

  SELECT INITCAP('ASDEM') FROM EMP;

3).查询姓名全部大写的员工的信息

  SELECT * FROM EMP WHERE ENAME=UPPER(ENAME);  

注意：
1).该函数含有一个参数
2).将姓名列的首字母大写尾字母大写，其他字母小写？？

3.求字符长度

字符集与字符字节的关系：
UTF-8 :一个汉字占用三个字节 --常用
ZHS16GBK：一个汉字占用两个字节 --常用
WE8ISO88S9PI:一个汉字占用一个字节 –罕见

---

LENGTH(STR):求取字符串STR 的长度
LENGTHB(STR): 求字符串的字节长度

示例：
1).
SELECT LENGTH(‘ADMIN’) FROM DUAL;
SELECT LENGTH(‘67班’) FROM DUAL;

 SELECT LENGTHB('67班') FROM DUAL;

---

–字符型数据的长度指的是字节长度

 SELECT LENGTH('Mr.迈克尔') FROM DUAL;
 SELECT LENGTHB('Mr.迈克尔') FROM DUAL;  
 SELECT * FROM EMP;
 INSERT INTO EMP(EMPNO,ENAME)VALUES(1002,'Mr.迈克尔')

---

4.替换函数

REPLACE(STR,S1,S2)

STR:要处理的字符串
S1:被替换的字符
S2：替换的字符

----将STR中的S1替换成S2

A.参数个数
将’ASDEFGH’中的DE替换为JI
SELECT REPLACE(‘ASDEFGH’,‘DE’,‘JI’) FROM DUAL;
SELECT REPLACE(‘ASDEFGH’,‘DE’) FROM DUAL;–用空值替换DE
SELECT REPLACE(‘ASDEFGH’) FROM DUAL;–报错 函数 没有足够的参数
SELECT REPLACE(‘ASDEFGH’,‘DE’,‘JI’,‘5’) FROM DUAL;–报错 函数参数过多

SELECT REPLACE('ASDEADD','D','J') FROM DUAL;

–函数最少两个参数，至多三个参数

B.单个字符替换
将’ASDASDDEFF’中的D替换为H
SELECT REPLACE(‘ASDASDDEFF’,‘D’,‘H’) FROM DUAL;

–替换时从字符串左侧开始找相同的字符，有一个替换一个，直到最后

C.多个字符的替换

将’ASDASDASDE’中的ASD替换为’团灭’
SELECT REPLACE(‘ASDASDASDE’,‘ASD’,‘团灭’) FROM DUAL;
SELECT REPLACE(‘ASASDASDASDE’,‘ASD’,‘团灭’) FROM DUAL;

 SELECT REPLACE('ASASDASDASDE','A','团灭') FROM DUAL;

----替换时从字符串左侧开始找相同的字符，将多个字符串看做为一个整体，替换

5.去除函数：

–语义
LTRIM(STR,S)：去除字符串STR中左侧的S，如果S参数省略，则默认去除空格。
RTRIM(STR,S)：去除字符串STR中右侧的S，如果S参数省略，则默认去除空格。
TRIM([LEADING/TRAILING/BOTH] [S FROM] STR)：去除字符串STR中的S，具体去除左侧/右侧/两侧

根据参数[LEADING/TRAILING/BOTH] 决定，若省略该参数，默认去除两侧；
参数[S FROM] 决定了被去除的内容，若省略则默认去除空格。

示例：
1).去除’ ASD ASD ’ 两侧的空格
SELECT TRIM(’ ASD ASD ') FROM DUAL;

 2).去除'  ASD  ASD  ' 左侧的空格
    SELECT LTRIM('  ASD  ASD  ') FROM DUAL; 
  
 3).去除'  ASD  ASD  ' 右侧的空格
    SELECT RTRIM('  ASD  ASD  ') FROM DUAL; 

-------A.参数情况
–包含一个参数
SELECT RTRIM(’ ASD ASD ‘) FROM DUAL;
–两个参数
SELECT RTRIM(’ ASD ASD ',‘A’) FROM DUAL;
–函数最少一个参数，用于去除空格；最多两个参数，用于去除两侧字符串

-------B.去除字符
–单个字符
将’ASDASDEF’中左侧的A去除
SELECT LTRIM(‘ASDASDEF’,‘A’) FROM DUAL;

SELECT TRIM(LEADING 'A' FROM 'AAASAAA') FROM DUAL;

将’ASDASDEF’中右侧的F去除
SELECT RTRIM(‘ASDASDEF’,‘F’) FROM DUAL;

SELECT TRIM(TRAILING 'A' FROM 'ASDASDEFA') FROM DUAL;

将两侧的A去除
SELECT RTRIM(LTRIM(‘ASDASDEFA’,‘A’),‘A’) FROM DUAL;
SELECT TRIM(‘A’ FROM ‘ASDASDEFA’) FROM DUAL;

SELECT TRIM(BOTH 'A' FROM 'ASDASDEFA') FROM DUAL;

–单个字符在TRIM时，会从字符串左侧、右侧、两侧查找要去除的字符，去除，直到找到第一个不等于第二个参数的字符

–多个字符

将字符 ‘ASD ASD ASD’中左侧的ASD去除
SELECT LTRIM(‘ASD ASD ASD’,‘ASD’) FROM DUAL;

 SELECT LTRIM('SAD ASD ASD','ASD') FROM DUAL;
 SELECT LTRIM('AAA ASD ASD','ASD') FROM DUAL;
 SELECT LTRIM('AAED ASD ASD','ASD') FROM DUAL;

–多个字符去除时，将要去除的字符看做为一个集合，从左、右、两侧开始去除字符，直到找到第一个非集合内的字符为止

6.填充函数

LPAD(STR,N,STR1)/RPAD(STR,N,STR1)

  STR：要处理的字符串
  STR1:要填充的字符/字符串
  N   :填充以达到N长度

—在STR左/右侧添加字符STR1使总长度达到N

A.参数情况

1)参数个数
–在‘asdasd’的左侧、右侧添加1，使长度达到8
SELECT LPAD(‘ASDASD’,8,1) FROM DUAL;
SELECT RPAD(‘ASDASD’,8,1) FROM DUAL;

SELECT LPAD(‘ASDASD’,8) FROM DUAL;–填充 空格
SELECT LPAD(‘ASDASD’) FROM DUAL;–报错 参数不够
SELECT LPAD(‘ASDASD’,8,1,‘1’) FROM DUAL;–报错 参数过多

–函数 最少两个参数 至多三个参数

2).N的大小
–N>LENGTH(STR)
SELECT LPAD(‘ASDASD’,8,1) FROM DUAL;
–0<N<LENGTH(STR)
SELECT LPAD(‘ASDASD’,4,1) FROM DUAL;–不填充，只截取出该长度的字符串
–N<=0
SELECT LPAD(‘ASDASD’,-1,1) FROM DUAL;–结果为空

B.单个字符填充
–在‘asdasd’的左侧、右侧添加1，使长度达到8
SELECT LPAD(‘ASDASD’,8,1) FROM DUAL;
SELECT RPAD(‘ASDASD’,8,1) FROM DUAL;

C.多个字符的填充

–在‘asdasd’的左侧、右侧添加1，使长度达到8
SELECT LPAD(‘ASDASD’,8,12) FROM DUAL;
SELECT LPAD(‘ASDASD’,9,12) FROM DUAL;
SELECT LPAD(‘ASDASD’,9,21) FROM DUAL;

–先计算到达N个字符时缺少几个字符，从填充的字符中自左向右取出该长度的字符填充

7.截取函数

SUBSTR(STR,IND,LEN)

STR:要处理的字符串
IND:开始截取字符的位置
LEN:截取字符的长度

–从字符串STR的IND位置，截取LEN长度的字符串并返回该截取内容 返回的是字符串

A.参数情况

1).参数个数
–从字符串‘ASDEFGHTING’的第5个位置截取长度为2的字符
SELECT SUBSTR(‘ASDEFGHTING’,5,2) FROM DUAL;

 SELECT SUBSTR('ASDEFGHTING',5,2,'5') FROM DUAL; --报错 参数过多

–省略参数LEN 默认从该位置截取，一直到最后
SELECT SUBSTR(‘ASDEFGHTING’,5) FROM DUAL;
–省略参数IND和LEN
SELECT SUBSTR(‘ASDEFGHTING’) FROM DUAL;–报错 参数不够

–函数最少有两个参数，至多三个

B.IND 的情况

–IND >LENGTH(STR)

SELECT SUBSTR(‘ASDEFGH’,8) FROM DUAL;–空值

–0<IND<=LENGTH(STR)

SELECT SUBSTR('ASDEFGH',7) FROM DUAL;   
SELECT SUBSTR('ASDEFGH',6) FROM DUAL 

–IND<=0
SELECT SUBSTR(‘ASDEFGH’,0) FROM DUAL–等于0时，默认从1开始截取

SELECT SUBSTR(‘ASDEFGH’,-2) FROM DUAL–小于0时，从右往左数位置

SELECT SUBSTR(‘ASDEFGH’,-8) FROM DUAL–空值

C.LEN的情况

–LEN>=LENGTH(STR)-IND+1

SELECT SUBSTR(‘ASDEFGH’,2,8) FROM DUAL–默认 截取到字符串最后为止
SELECT SUBSTR(‘ASDEFGH’,2,7) FROM DUAL

– 0<LEN<LENGTH(STR)-IND+1

SELECT SUBSTR(‘ASDEFGH’,2,4) FROM DUAL–截取的字符长度等于LEN

–LEN<=0

SELECT SUBSTR(‘ASDEFGH’,2,0) FROM DUAL;–空值
SELECT SUBSTR(‘ASDEFGH’,2,-1) FROM DUAL ;–空值

8.获取字符的位置

INSTR(STR,STR1,IND,N)

STR :要处理的字符串
STR1：要查找的字符
IND： 查找字符开始的位置
N： 查找字符第几次出现

—从IND位置开始查找STR中 STR1第N次出现的位置 返回的是个数值 (数值=从左到右该字符的位置)

A.参数情况
1).参数的个数

–查找‘ASDEFGH’中H的位置
SELECT INSTR(‘ASDEHFGH’,‘H’,1,2) FROM DUAL;

–省略N参数

SELECT INSTR(‘ASDEHFGH’,‘H’,1) FROM DUAL;–默认为第一次出现的位置

–省略IND和N

SELECT INSTR(‘ASDEHFGH’,‘H’,2) FROM DUAL;–默认将 N参数补充到IND位置
SELECT INSTR(‘ASDEHFGH’,‘H’) FROM DUAL;–默认 第一位第一次出现

–省略STR1和IND和N

SELECT INSTR(‘ASDEHFGH’) FROM DUAL; --报错

–函数至少两个参数，至多4个参数

B.STR1的情况

1).STR1为空

SELECT INSTR(‘ASDEHFGH’,’’,1,1) FROM DUAL;–空

2).单个字符

SELECT INSTR(‘ASDEHFGH’,‘H’,1,2) FROM DUAL;–为字符H的位置

3).多个字符

SELECT INSTR(‘ASDEHFGH’,‘HF’,2) FROM DUAL;–返回的为多个字符中第一个字符的位置

C.IND的情况

–IND>LENGTH(STR)

SELECT INSTR(‘ASDEH’,‘H’,6) FROM DUAL; --返回 0

–0<IND<=LENGTH(STR)

SELECT INSTR(‘ASDEHFGH’,‘H’,2) FROM DUAL;
SELECT INSTR(‘ASDEHFGH’,‘H’,5) FROM DUAL;
SELECT INSTR(‘ASDEHFGH’,‘H’,6) FROM DUAL;

–找到字符 返回该字符的位置，找不到 返回 0

–IND<=0

SELECT INSTR(‘ASDEHFGH’,‘H’,0) FROM DUAL;–为0
SELECT INSTR(‘ASDEHFGH’,‘H’,-2) FROM DUAL;–5
SELECT INSTR(‘HASDEFGH’,‘H’,-2) FROM DUAL;–1

–IND为负数时，从右向左查找字符，起始位置为 |IND|

D.N的情况

–N>N(STR)

SELECT INSTR(‘ASDEHFGH’,‘H’,6,2) FROM DUAL;–找不到 返回0

–0<N<=N(STR)

SELECT INSTR(‘ASDEHFGH’,‘H’,2,2) FROM DUAL;–8
SELECT INSTR(‘ASDEHFGH’,‘H’,2,1) FROM DUAL;–5

–N<=0

SELECT INSTR(‘ASDEHFGH’,‘H’,2,0) FROM DUAL; --报错
SELECT INSTR(‘ASDEHFGH’,‘H’,2,-1) FROM DUAL;–报错
–N 不可以小于等于 0

四.小结

1.清楚函数的定义，会用函数

2.字符型函数

1).拼接函数
A.参数只有两个
B.若拼接多个字符需要嵌套函数

2).大小写函数

3).求取长度

A.LENGTH(STR)求字符STR的长度
B.lengthb（str）求字符的字节长度

4).替换函数

A.函数中最少两个参数，至多三个参数
B.单个字符替换时从字符串左侧开始找相同的字符，有一个替换一个，直到最后
C.多个 字符替换时从字符串左侧开始找相同的字符，将多个字符串看做为一个整体，替换

5).去除函数
A.语义：TRIM([LEADING/TRAILING/BOTH] [S FROM])
去除字符串STR中的S，具体去除左侧/右侧/两侧根据参数[LEADING/TRAILING/BOTH] 决定，若省略该参数，
默认去除两侧； 参数[S FROM] 决定了被去除的内容，若省略则默认去除空格

B.去除空格
LTRIM(STR)/RTRIM(STR)

C.去除字符
LTRIM(STR,S)/RTRIM(STR ,S)

D.个字符去除时，将要去除的字符看做为一个集合，从左、右、两侧开始去除字符，直到找到
第一个非集合内的字符为止

6).填充函数

A.最少两个参数,至多三个参数.
B.先计算到达N个字符时缺少几个字符，从填充的字符中自左向右取出该长度的字符填充

7).截取函数

A.从字符串STR的IND位置，截取LEN长度的字符串并返回该截取内容 返回的是字符串
B.函数最少有两个参数，至多三个
C.IND<=0,-等于0时，默认从1开始截取 小于0时，从右往左数位置
D.LEN>=LENGTH(STR)-IND+1 默认 截取到字符串最后为止
E.0<LEN<LENGTH(STR)-IND+1 截取的字符长度等于LEN
F. LEN<=0 空值

8).获取字符位置

A.从IND位置开始查找STR中 STR1第N次出现的位置 返回的是个数值 (数值=从左到右该字符的位置)
B.函数至少两个参数，至多4个参数;
C.IND为负数时，从右向左查找字符，起始位置为 |IND|,返回的是个数值 (数值=从左到右该字符的位置)