oracle常用函数汇总【持续更新中…】[亲测有效]

一、listagg() WITHIN GROUP () 行转列函数的使用（需要将某个字段分组后，另外一个字段逗号拼接起来，同WM_CONCAT,但是有的oracle版本不支持WM_CONCAT函数）

SQL:

select domain_id, listagg(org_id, ‘,’) within GROUP( ORDER BY org_id) org_id  from **表 where  domain_id =  ‘0c8ef-4u38f0-00f9m’ GROUP BY DOMAIN_ID;

扩展:如果遇到聚合函数返回的值长度过长这时候listagg函数就会报:字符串连接的结果过长，类似这种报错

解决方式：

用下面函数替换

(xmlagg(xmlparse(content file_Id||’,’ wellformed) order by file_Id).getclobval()) as file_Ids

2.wm_concat行转列函数,oracle独有函数，测试了下Oracle12版本就不支持此函数了

sql举例:
SELECT  FILE_CONFIG_BUS_ID,wm_concat(file_type_id) FROM  “PROJ_FILE_CONFIG_BUS_FILE_TYPE”
GROUP BY FILE_CONFIG_BUS_ID;

3.instr()函数的格式  （俗称：字符查找函数）

格式一：instr( string1, string2 )    // instr(源字符串, 目标字符串)

表示在string1中查找string2第一次出现的位置，返回查到的位置，没查到返回0

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )   // instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string，返回查到的位置，没查到返回0

like 和  instr() 函数有同样的查询效果； 如下所示：

select * from tableName where name like ‘%helloworld%’;

select * from tableName where instr(name,’helloworld’)>0; –这两条语句的效果是一样的

4.oracle如何将字符型字段按照数字排序

SELECT  task_no,TASK_NAME  FROM “PROJ_TASK” where TASK_PID = ‘0a27h-8aumf0-00vg5’ order by to_number(translate(task_no, ‘0123456789.’ || task_no, ‘0123456789.’)) asc nulls last
主要为标红处