Oracle常用函数总结

1 nvl:根据参数1是否为null返回参数1或参数2的值

语法:nvl(expr1,expr2)

含义是:如果oracle第一个参数为null那么显示第二个参数的值;
select nvl(null, '12345') from dual;  --返回 12345

如果第一个参数的值不为null,则显示第一个参数本来的值。
select nvl('ABCD','abcd') from dual; --返回ABCD

2 nvl2:根据参数1是否为null返回参数2或参数3的值

语法:nvl2(expr1,expr2, expr3)

含义是:如果该函数的第一个参数为null那么显示第三个参数的值,
select nvl2(null, '12345', 'abcd') from dual;  --返回abcd

如果第一个参数的值不为null,则显示第二个参数的值。
select nvl2('ABCD', '12345', 'abcd') from dual; --返回 12345

3 union与union all 用法区别

1union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
2union all:对两个结果集进行并集操作,包括重复行,不进行排序;
3intersect:对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序;
4)minus:对两个结果集进行差操作,不包括重复行,同时进行默认规则的排序。

4 substr 函数:截取字符串

语法:substr(string,start, [length])
string:表示源字符串,即要截取的字符串;
start:开始位置,从1开始查找,如果start是负数,则从string字符串末尾开始算起;
length:可选项,表示截取字符串长度;

select substr('hello sql!', 1) from dual;         --截取所有字符串,返回  hello sql!
select substr('hello sql!', 2) from dual;         --从第2个字符开始,截取到末尾,返回 ello sql!
select substr('hello sql!', -4) from dual;        --从倒数第4个字符开始,截取到末尾,返回 sql!
select substr('hello sql!', 3, 6) from dual;     --从第3个字符开始,截取6个字符。返回 llo sq
select substr('hello sql!', -4, 3) from dual;   --从倒数第4个字符开始,截取3个字符,返回 sql

5 instr 函数:返回子字符串在源字符串中的位置

语法:instr(string,child_string,[start][show_time])
string:表示源字符串;
child_string:子字符串,即要查找的字符串;
start:可选项,开始位置,默认从1开始,如果为负数,则从右向左检索;
show_time:可选项,表示子字符串第几次出现在源字符串当中,默认第1次,负数则报错;

注:substr 和 instr两个函数可以结合使用满足需求。

6 concat 函数:拼接字符

语法:concat(c1, c2)
功能:c1,c2均为字符串,函数将c2连接到c1的后面,如果c1为null,将返回c2,如果c2为null,则返回c1,如果c1、c2都为null,则返回null
select concat('A', 'B') from dual; --返回 AB
select concat(null, 'B') from dual; --返回 B
select concat('A', null) from dual; --返回 A
select concat(null, null) from dual; --返回 null

7 replace 函数:字符替换

语法:replace(str1,str2,str3) 如:replace('ABCDE','CD','AAA')
功能:将str1中所有出现的str2替换为str3
select replace('ABCDE', 'CD', 'AAA') from dual; --返回 ABAAAE

8 length 函数:获取字符长度

语法:length(string)
功能:计算string的长度
select length('ABCDE') from dual;  --返回 5

9 lower 函数:字符转小写

语法:lower(string)
功能:将string全部转小写
select lower('ABCDE') from dual; --返回 abcde

10 upper 函数:字符转大写

语法:upper(string)
功能:将string全部转大写
select upper('abcde') from dual; --返回 ABCDE

11 initcap 函数:首字母大写

语法:initcap(string)
功能:将string首字母大写
select initcap('abcde') from dual; --返回 Abcde

12 rpad、lpad 函数:填补

语法:rpad(str1,int,str2) 、lpad(str1,int,str2) 
str1:原字符串
int:补充后达到的个数
str2:补充的字符
功能:
rpad:在str1右边填补str2字符,填补到int位数
lpad:在str1左边填补str2字符,填补到int位数

select rpad('abcde',2,'ab') from dual;  --int小于str1的总数,返回 ab
select rpad('abcde',5,'ab') from dual;  --int等于str1的总数,返回 abcde
select rpad('abcde',8,'ab') from dual;  --int大于str1的总数,返回 abcdeaba

select lpad('abcde',2,'ab') from dual;  --int小于str1的总数,返回 ab
select lpad('abcde',5,'ab') from dual;  --int等于str1的总数,返回 abcde
select lpad('abcde',8,'ab') from dual;  --int大于str1的总数,返回 abaabcde

13 ltrim、rtrim 函数:删除字符

语法:ltrim(str1,str2)、 rtrim(str1,str2) 
功能:
ltrim删除str1字符中左边的str2字符
rtrim删除str1字符中右边的str2字符
     
select rtrim('11******', '*') from dual; --返回 11
select ltrim('**11******', '*') from dual; --返回 11******

14 trim 函数:去除空格

语法:trim(str)
功能:去除str中的空格
select trim('  111  ') from dual; --返回 111

15 row_number() over(partition by 列名1 order by 列名2 desc)的使用

该函数表示根据 列名1 分组,然后在分组内部根据  列名2 排序;
而此函数计算的值就表示每组内部排序后的顺序编号,可以用于去重复值与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码。

查询所有姓名,如果同名,则按年龄降序
select name,
       age,
       detail,
       row_number() over(partition by name order by age desc)
  from test_y;
  
注释:通过上面的语句可知,是按照name字段分组,按age字段排序的。

如果只需查询出不重复的姓名即可,则可使用如下的语句, 由查询结果可知,姓名相同年龄小的数据被过滤掉了;
select *
  from (select name,
               age,
               detail,
               row_number() over(partition by name order by age desc) rn
          from test_y)
 where rn = 1;

 

 

16 round函数

round函数返回一个数值,该数值是按照指定的小数位数进行四舍五入运算的结果。除数值外,也可对日期进行舍入运算。
语法:round(number,num_digits),即:round(数值,保留的小数位数)
number:需要进行四舍五入的数字;
num_digits:指定的位数,按此位数进行四舍五入;
其中,如果 num_digits 大于 0,则四舍五入到指定的小数位;
如果 num_digits 等于 0,则四舍五入到最接近的整数;
如果 num_digits 小于 0,则在小数点左侧进行四舍五入。
select round(3.19, 1) from dual;            --将 3.19 四舍五入到一个小数位,返回结果 (3.2)
select round(2.649, 1) from dual;          --将 2.649 四舍五入到一个小数位,返回结果 (2.6)
select round(-5.574, 2) from dual;        --将 -5.574 四舍五入到两小数位 ,返回结果(-5.57)
select round(18.8, -1) from dual;          --将 18.8 四舍五入到小数点左侧一位 (20),这个参数-1表示取整到十位数。

参考博文:

https://blog.csdn.net/qq_44696583/article/details/123320244

https://blog.csdn.net/weixin_55175040/article/details/117997414

 

posted @ 2022-09-05 11:16  DAYTOY-105  阅读(134)  评论(0编辑  收藏  举报