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④如果找不到匹配数据就返回0SQL> 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 asselect '1' stu_id, '张三' stu_name from dualunion allselect '2', '李四'  from dualunion allselect '3', '王五' from dualunion allselect '4', '赵六' from dual;create table class asselect '1' cls_id,'1,2,3,4' stu_ids from dualunion allselect '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,42  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,42  1,4 ①select regexp_substr(string, '[^,]+', 1, level)  from dualconnect 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 dualconnect 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 dualconnect by level <= regexp_count('i,am,a,pretty,girl', '[^,]+')   order by 1, 2; 'I,AM,A,PRETTY,GIRL'                 SUBRING------------------------------------ -----------------i,am,a,pretty,girl                   ai,am,a,pretty,girl                   ami,am,a,pretty,girl                   girli,am,a,pretty,girl                   ii,am,a,pretty,girl                   prettyselect 'i,am,a,pretty,girl',       regexp_substr('i,am,a,pretty,girl', '[^,]+', 1, level)  from dualconnect 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                   ii,am,a,pretty,girl                   ami,am,a,pretty,girl                   ai,am,a,pretty,girl                   prettyi,am,a,pretty,girl                   girl   select s.stu_ids,regexp_substr(s.stu_ids, '[^,]+', 1, level)  from class sconnect 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 sconnect 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----enN2:手机号中间五位数用星号替换with t1 as (select '13791132771' phone  from dualunion allselect '15854117963' phone  from dual)select substr(phone,1,3)||'*****'||substr(phone,-3,3) phone from t1;PHONE-----------------------------137*****771158*****963N3：报表中展示不方便，需要处理一下，只展示域名

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