instr+listagg+正则表达式+substr函数+translate

1、instr 函数的用法:
（1）：instr(string,sr,i,j)
string 被搜索的字符串,sr是希望搜索的字符串,i是搜索开始位置，默认为1，j是sr出现的位置，第n次出现，默认是1
①
这里是设置为默认值，从第一个字母开始的位置，第一次出现is的位置。
SQL>   select instr('Mississippi','is') instring from dual;

  INSTRING
----------
         2
②	从第一个字母开始，第一次出现is字母的位置 
	 

SQL> select instr('Mississippi','is',1,2) instring from dual;

  INSTRING
----------
         5
③从第2个字母开始搜索，第二次出现is的位置	

SQL> select instr('Mississippi','is',2,2) instring from dual;

  INSTRING
----------
         5
④如果找不到匹配数据就返回0

SQL> select instr('Mississippi','hh') instring from dual;

  INSTRING
----------
         0 
 ⑤：从右开始数，出现第一次出现is的位置

SQL>  select instr('Mississippi','is',-1,1) instring from dual;

  INSTRING
----------
         5
例子：
create table student as
select '1' stu_id, '张三' stu_name from dual
union all
select '2', '李四'  from dual
union all
select '3', '王五' from dual
union all
select '4', '赵六' from dual;
create table class as
select '1' cls_id,'1,2,3,4' stu_ids from dual
union all
select '2' ,'1,4' from dual;

SQL> select * from student;

stu_id STU_NAME
--     ------------
1        张三
2        李四
3        王五
4        赵六

SQL> select * from class;

CL STU_IDS
-- --------------
1  1,2,3,4
2  1,4
得到结果：
 cls_id   stu_name
    1    张三李四王五赵六
    2    张三赵六
这里使用了listagg函数和instr函数
最基础的用法：
LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)
方法1：
select cls_id,
       listagg(stu_name) within group(order by s.cls_id ) stu_name
  from class s , student t
 where instr(stu_ids, STu_id) > 0
 group by cls_id
方法2：
with a as
 (select CLS_ID, regexp_substr(STU_IDS, '[^,]+', 1, level) STU_IDS
    from class
  connect by level <= regexp_count(STU_IDS, ',') + 1
         and rowid = prior (rowid)
         and (prior dbms_random.value) is not null)
select a.CLS_ID, listagg(b.STU_NAME,',') within group(order by b.STU_ID)
  from a
 inner join student b
    on (a.STU_IDS = b.STU_ID) group by a.CLS_ID;
	



2、拆分函数 +listagg函数多列转一行，使用正则表达式

CL STU_IDS            
-- --------------
1  1,2,3,4
2  1,4

 
①select regexp_substr(string, '[^,]+', 1, level)
  from dual
connect by level <= regexp_count(string, '[^,]+') + 1
   and rowid = prior (rowid)
         and (prior dbms_random.value) is not null;
②select regexp_substr(string, '[^,]+', 1, level)
  from dual
connect by level <= length(string) - LENGTH(REPLACE(string, ',', '')) + 1
 and rowid = prior (rowid)
         and (prior dbms_random.value) is not null;

REGEXP_SUBSTR 函数是把那个串以正则不是以,
（逗号）开头的截取，第二个参数是取第几组，level，connect 循环 ，
循环次数为串总长度-去除分隔符后=几个分隔符 +1
 select 'i,am,a,pretty,girl' ,
       regexp_substr('i,am,a,pretty,girl', '[^,]+', 1, level) subring
  from dual
connect by level <= regexp_count('i,am,a,pretty,girl', '[^,]+')  
 order by 1, 2;
 
'I,AM,A,PRETTY,GIRL'                 SUBRING
------------------------------------ -----------------
i,am,a,pretty,girl                   a
i,am,a,pretty,girl                   am
i,am,a,pretty,girl                   girl
i,am,a,pretty,girl                   i
i,am,a,pretty,girl                   pretty

select 'i,am,a,pretty,girl',
       regexp_substr('i,am,a,pretty,girl', '[^,]+', 1, level)
  from dual
connect by level <= length('i,am,a,pretty,girl') -
           LENGTH(REPLACE('i,am,a,pretty,girl', ',', '')) + 1;
		   
		   

'I,AM,A,PRETTY,GIRL'                 REGEXP_SUBSTR('I,AM,A,PRETTY,GIRL','[^,]+',1,LEVEL)
------------------------------------ --------------------------------------------------------
i,am,a,pretty,girl                   i
i,am,a,pretty,girl                   am
i,am,a,pretty,girl                   a
i,am,a,pretty,girl                   pretty
i,am,a,pretty,girl                   girl
		   

select s.stu_ids,regexp_substr(s.stu_ids, '[^,]+', 1, level)
  from class s
connect by level <= length(s.stu_ids) - LENGTH(REPLACE(s.stu_ids, ',', '')) + 1
 and rowid = prior (rowid)
         and (prior dbms_random.value) is not null;

select regexp_substr(s.stu_ids, '[^,]+', 1, level)
  from class s
connect by level <= regexp_count(s.stu_ids, '[^,]+') + 1
 and rowid = prior (rowid)
         and (prior dbms_random.value) is not null;



 3、substr截取字符串函数

 N1：取得字符串中指定起始位置和长度的字符串   substr( string, start_position, [ length ] )

SQL>  select substr('ename',1,2) substring  from dual;

SUBS
----
en


N2:手机号中间五位数用星号替换
with t1 as 
(
select '13791132771' phone
  from dual
union all
select '15854117963' phone
  from dual)
select substr(phone,1,3)||'*****'||substr(phone,-3,3) phone from t1;
PHONE
-----------------------------
137*****771
158*****963

N3：报表中展示不方便，需要处理一下，只展示域名

with r as
 (select 'https://so.m.sm.cn/s?q=%E9%98%B3%E5%85%89%E4%BF%9D%E9%99%A9%E7%90%86%E8%B4%A2%E4%BA%A7%E5%93%81uc_param_str=dnntnwvepffrgibijbprsvdsmefrom=ucframeuc_sm=1' referer
    from dual
  union all
  select 'http://www.sogou.com/bill_cpc?v=1p=WJ80$xbzZtAewVlkq3aehWy0@l$2Aey1MP7n2Rwzb5OMihL8etVEz1fenuhE170IGg4qc$AlAYDm5u2GWadDc0Kn0pQtxYiqbassEsCZMutFo1JeKXIM$Qiitgp$r$VBU6$AmBVIY$$JvQ3ztB3mkYpOBSW0bUBJvYpqsXiiWbOWSOIAPqihK$iMyqVMbXiDyqQhg6$M$rVbzqVnyQJSY$iH74mlropPPzfJuYQm7BpJmN69eHDHxiW$8uJ628yC72FJaJxAiLw79uwh2JgAcLaG9nw12Rt8G8w1G8QgYm2Aax2A2lFShU3G@OwzgjZVBB6IykopzUYpxPzuO7uuIAzp3YYuxAxkj9aui6YpOoBlwiXvvcyYRzScmJk37cQksk5hpjm9Lw1G0lhGRvexs9o98cja3vCNvjsSLjrS37U$VUkuQQUlvyPGkmG6GJCoXv0VhDqvUIez3GS$oln9UHetaGgVuyBj89BuJO3tgjhIhtOkJ1jYuyPuGo0JZtqtulCutgneIqzUenKXkFkjyxQIeQOj4wBuiIbpB$Bpw3d6Vl2Rlyxqhwzpq=WJe0lllllylxquery=%E6%83%A0%E9%87%91%E6%89%80ml=75mc=235ma=680,0,300,160,294,149,1263,561block=380,292,-1,-1'
    from dual
  union all
  select 'https://www.hfax.com/promoapp_lzz.do?urlparam=DDP-lzz-002-10002'
    from dual)
select substr(referer,pos1,pos2) from (
select referer,instr(referer, 'http', 1) pos1, instr(referer, '/', 1, 3) pos2
  from r)


4、一个字符替换函数translate，不同于replace函数的是，translate函数是字符级别的替换，而不是字符串的替换。

其语法如下：


TRANSLATE ( expr , from_string , to_string )


简单的说就是对expr内容，用to_string中的字符逐一替换from_string 中的字符
SQL> select translate('173655294','345','abc') from dual;
TRANSLATE('173655294','345','A
------------------------------
17a6cc29b