Oracle-21-SQL单行函数之字符函数

一、SQL函数

Oracle提供了丰富的内建函数。一个函数接受0个或多个输入值，并返回一个输出值。Oracle函数分为两组：单行函数(singlerow function)和聚合函数(aggregatefunction)。

 

单行函数：一次只操作一行并且针对每个出书行返回一行。

聚合函数：同时对多个行进行操作并返回一行输出结果。

 

二、单行函数

Oracle有许多单行函数，可归纳为以下几类：

字符函数：主要是操作字符串

数字函数：主要是操作数字类型的数值

转换函数：这些函数将数据从一种数据类型转换为另一种数据类型

日期函数：处理日期和时间

正则表达式函数：这些函数使用正则表达式搜索数据

 

三、单行函数之字符函数

首先给出常用的字符函数：

1. concat(m,n)——将m和n连接起来，并返回连接后的字符串；

2. initcap(n)——将n中的第一个字母转换成大写；

3. instr(x,y,m,n)——在x中查找字符串y出现的位置。其中m,n是可选项，m代表从字符串x的第m个位置开始查找，n代表字符串y出现次数；

4. length(n)——求字符串n的长度；

5. lower(n)——将字符串n中各个字符转换成小写；

6. lpad(x,n,y)——在字符串x的左边补齐空格，得到总长度为n个字符的字符串。其中y是可选项，这个参数用于指定左边补齐的字符串；

7. rpad(x,n,y)——类似于lpad，只是在字符串x的右边补齐；

8. trim函数：

trim(leading 指定字符 from 字符串)是去掉字符串左边的指定字符；

trim(trailing 指定字符 from 字符串)是去掉字符串右边的指定字符；

trim(both 指定字符 from 字符串)是去掉字符串左右两边的指定字符。

9. ltrim(x,y)——在x左边截去指定的字符。指定字符可以通过参数y来表示。如果没有写参数y，则默认截去空格。

10. rtrim(x,y)——类似于ltrim，只是在字符串x的右边截去指定字符。

11. nvl(x,y)——如果x为null，则结果返回y，否则结果返回x。

12. nvl2(x,y,z)——如果x不为null，则结果返回y，否则返回z。

13. nanvl(x,y)——如果x的值是NAN（非数字），则结果返回y；否则结果返回x。

14. replace(x,y,z)——在x中将y替换为z。

15. upper(x)——将字符串n中各个字符转换成大写。

16. substr(x,m,n)——从x中的m位置截取长度为n的子串，如果n省略不写，那么子串就是从x中的m位置一直取到结尾

 

例1：练习concat(m,n)函数。

解：先看下之前讲的连接两个字符串的方法：

取别名：

用concat(m,n)函数可以达到同样的效果：

另外在concat(m,n)函数里也可以传入列名：

例2：练习initcap(n)函数（只是将第一个字母小写转大写，后面不变）

解：首先查看当前student表全部信息：

将第一列数据每行首字母转换成大写：

例3：练习instr(x,y,m,n)函数

解：首先查看当前student表全部信息：

查询sno列0出现的位置：

instr(sno,’0’,1,2)表示在sno列中查找字符0，从第1个位置开始查找其第2次出现的位置。比如上图中第一行查找到位置3，也就是’s001’中第2次出现0的位置是3。为方便对比，将sno列也查出来：

如果此时instr(sno,’0’)表示在sno列从头开始查找0第一次出现的位置：

【函数instr总结】

（1）instr(x,y,m,n)表示在x中查找y，从m位置开始查找，找出其第n次出现的位置，其中x可以是列名；

（2）instr(x,y)表示在x中从查找y，从头开始查找，找出其第1次出现的位置，其中x可以是列名。

 

例4：练习length(n)函数

解：计算长度

 

例5：练习lower(n)函数

解：所有字母全部转换成小写

 

例6：练习lpad和rpad函数

解：

【lpad和rpad总结】

（1）lpad(sno,6,’*’)表示对于sno列的数据，取6个长度，长度不足的数据在其左边补*；

（2）lpad(sno,6)表示对于sno列的数据，取6个长度，长度不足的数据在其左边补空格；

（3）rpad(sno,6,’*’)表示对于sno列的数据，取6个长度，长度不足的数据在其右边补*；

（4）rpad(sno,6)表示对于sno列的数据，取6个长度，长度不足的数据在其右边补空格。

注：若sno列有长度超过6的数据，那么从头开始取6个长度字符串，比如下图：

 

例7：练习trim函数

解：

 

例8：练习nvl函数

解：首先查询当前用户下的player表：

发现game_id列有null值，那么用nvl函数：

nvl(game_id,1002)函数判断game_id列是否有null数据，如果有则返回1002，否则返回原表中非空数据。

 

例9：练习nvl2函数

解：首先查询当前用户下的player表：

用nvl2函数：

图中nvl2(game_id,1000,1005)函数意思是在查询game_id列数据时，对于非空数据将其替换为1000（如刘翔本来是1001现在是1000），对于空数据将其替换为1005（如博尔特本来是null现在是1005）。

【注意】nvl和nvl2函数只是select语句，查询用，不会改变原表任何数据。

 

例10：练习replace函数

解：

【注意】replace函数只是select语句，查询用，不会改变原表任何数据。

 

例11：练习substr函数

解：首先从字符串’abcdef’的第2位置开始取长度为3的子串：

然后从字符串’abcdef’的第2位置开始取到末尾的子串：

最后提醒substr(x,m,n)中的n可以不写，n不写就表示从m位置一直取到最后，但是m参数必须写，如果不写，会报错，如：