Oracle数据库较为常用函数

oracle的基本函数使用

简单示例：

字符连接（跨行） wmsys.wm_concat，wm_concat

select wmsys.wm_concat(id1) from Temp;
select wm_concat(id1) from Temp;

字符连接（跨列） concat

select concat(id1 ,name3) from dual

查询字符的长度 LENGTH

SELECT LENGTH(‘ABBCDE’) from dual;

替换 TRANSLATE，replace

select TRANSLATE(‘1234’,‘123’,‘4’) from dual;
select replace(‘1234’,‘123’,‘4’) from dual;

oracle数据转换

title() 用法：首字母大写 title(xxxx)
lower() 用法：所有字母小写 lower(xxxx)
upper() 用法：所有字母大写 upper(xxxx)
to_char() 用法： 转化成字符串类型 to_char(xxxx)
to_number() 用法： 装换成数字类型 to_number(‘1’)
to_data() 用法： 装换成时间类型

create table Temp(
id1 int,
name2 varchar2(48),
id3 int,
name3 varchar2(48)
);
测试数据：
insert into Temp (id1,Name2,id3,Name3) values(1,'a',2,'b');
insert into Temp (id1,Name2,id3,Name3) values(2,'a',3,'b');
insert into Temp (id1,Name2,id3,Name3) values(3,'a',4,'b');

一、字符连接（跨行、跨列）

1、wmsys.wm_concat()，wm_concat，

用法：字段连接，连接跨行的同一列或多列字段 （只能连接同数据类型的）

注意:11gr2和12C上已经摒弃了wm_concat函数,所以要用连接函数,建议使用之前介绍的两种.如果之前老项目使用了这个函数,需要重建该函数或者在当前运行oracle版本中没有这个函数请看这 “WM_CONCAT”: 标识符无效

select wmsys.wm_concat(id1) from Temp;
select wmsys.wm_concat(id1,name3) from Temp;
select wmsys.wm_concat(id1 || '-' || name3) from Temp;
select  wm_concat(id1) from Temp;
select  wm_concat(id1,name3) from Temp;
select  wm_concat(id1 || '-' || name3) from Temp;

————————————————
原文链接：链接: Oracle各种连接函数和列转行总结

2、concat()

用法：跨列连接

select concat(id1 ,name3) from dual
select concat(id1 || ',',name3) from Temp  

3、LISTAGG(xxx,’,’) within group(order by xxx)

用法：跨行连接 需要进行分组

select id1,listagg(name3) within group(order by name3) from Temp group by id1;
select id1,listagg(name3,',') within group(order by name3) from Temp group by id1;

二、字段的字符串数量获取，长度获取

1、查询字符的长度 LENGTH(‘ABBCDE’)

SELECT LENGTH('ABBCDE') from dual;

2、查询字符串中字符B出现的次数： LENGTH(‘ABBCDE’)-LENGTH(REPLACE(‘ABBCDE’,‘B’,’’))

SELECT LENGTH('ABBCDE')-LENGTH(REPLACE('ABBCDE','B','')) FROM DUAL;

3、字符串的长度获取，获取对应的数量

create or replace function fn_substrnum(colname in varchar2,
                                     old_str in varchar2) 
                                     return varchar2 is
  subnum number(10);
  /**
  查找表中指定字段字符串的子字符串个数
  */
begin
  return nvl(length(colname),0)-nvl(length(replace(colname,old_str,'')),0); 
end fn_substrnum;

————————————————
链接: 字符串的长度获取，获取对应的数量

三、替换

1、替换 TRANSLATE(‘1234’,‘123’,‘4’)

用法： 1234是要操作的字段，123是筛选字段，将123的第一个1选中，去1234查询到1替换成4，再选中123里的2和3，在1234里删除2和3; 最终结果是 44;

SELECT LENGTH(TRANSLATE('ABBCDE','B'||'ABBCDE','B')) FROM DUAL;
select TRANSLATE('1234','123','4') from dual;
select TRANSLATE('abcdddaddd','bda','1') from dual;

2、替换 replace(‘1234’,‘123’,‘4’)

用法：替换1234里的123为4

select replace('1234','123','4') from dual;

四、或使用正则表达式：

1 字符数量统计：

1、字符数量统计 REGEXP_COUNT

select regexp_count('ABBCDE', 'B') from dual

————————————————
链接: ORACLE 查询某字符串中某字符出现的次数

五、 字符串特点条件截取

1、字符串特点条件截取regexp_substr：

select regexp_substr(‘1 2 3’,’[^ ]+’,1,level,‘i’) from dual

connect by level <= length(‘1 2 3’) -length(regexp_replace(‘1 2 3’,’ ‘,’’))+1;
这种方法的核心就是regexp_substr函数，通过正则来拆分字符串，函数用法为：

regexp_substr(str,进行匹配的正则表达式,position,标志第几个匹配组，modifier)。

该函数的一个缺点就是只能每次取一个字符串出来，这个就比较头痛了，因为现在我要取分割后所有的字符串，所以现在就要用到connect by命令，来限制取几个匹配组，当然我们是要全部的，所以就通过length来实时确定所取得匹配组数量。
————————————————
链接regexp_substr字符串条件截取

六、数据库数据转换

title() 用法：首字母大写 title(xxxx)

lower() 用法：所有字母小写 lower(xxxx)

upper() 用法：所有字母大写 upper(xxxx)

to_char() 用法： 转化成字符串类型 to_char(xxxx)

to_number() 用法： 装换成数字类型 to_number(‘1’)

to_data() 用法： 装换成时间类型

select to_date('2021/10/22 14:22:22','YYYY/mm/dd hh24:mi:ss') from dual

七、数据库sql的不为空转换

1、nvl()用法 ： nvl(xxx,0) 当xxx为空时，默认为零

select nvl(null,0) from dual;
select nvl("abc","a") from dual;

八、数据库sql的条件筛选查询

1、条件筛选 case when Then end

select  case when  1>0 then "a"  when 1<2 then "b" end;  from dual;
select  case when  1>0 then "a" else "b"  when 1<2 then "b" end;  from dual;
CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END;

————————————————
链接: 数据库sql的条件筛选

九、数据库sql的是否包含

1、是否包含 instr(str,b)

select 'abc' from dual where instr('abc','b')>0;
select 'abc' from dual where instr('abc','d')>0;

十、数据库sql的截取字符

1、截取字符 substr(‘abcdefg’,1,5)

select substr('abcdefg',1,5) from dual;

十一、数据库的小数取整函数

1、floor()，trunc() 向下取整

select floor(5.534) from dual;
select trunc(5.534) from dual;

结果：上面两种用法都可以对数字5.534向下取整，结果为5.

2、ceil() 向上取整

如果要向上取整 ，得到结果为6，则应该用ceil

select ceil(5.534) from dual;

3、round() 四舍五入

SELECT round(5.534) FROM dual;
SELECT round(5.534,0) FROM dual;
SELECT round(5.534,1) FROM dual;
SELECT round(5.534,2) FROM dual;

结果分别为 6, 6, 5.5, 5.53

4、保留N位小数（不四舍五入）：

select trunc(5.534,0) from dual;  -结果 5
select trunc(5.534,1) from dual;  -结果 5.5
select trunc(5.534,2) from dual;  -结果 5.53

结果分别是 5,5.5,5.53，其中保留0位小数就相当于直接取整了。

5、数字格式化：

5.1、结果为12345.123

select to_char(12345.123,‘99999999.9999’) from dual; -结果 12345.1230

5.2、小数后第三第四为不足补0

select to_char(12345.123,'99999999.9900') from dual;   --结果 12345.1230

5.3、

select to_char(0.123,'99999999.9900') from dual;  --结果    .1230
select to_char(0.123,'99999990.9900') from dual;  --结果   0.1230