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 用法区别
1)union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序; 2)union all:对两个结果集进行并集操作,包括重复行,不进行排序; 3)intersect:对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序; 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