INSTR字符串函数

  
       INSTR (源字符串, 目标字符串, 起始位置, 匹配序号)

　　在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，就是说从字符的开始到字符的结尾就结束。

　　语法如下：

　　

instr( string1, string2 [, start_position [, nth_appearance ] ] )

　　参数分析：

　　

string1：源字符串，要在此字符串中查找。
　　string2: 要在string1中查找的字符串.
　　start_position: 代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。
　　nth_appearance： 代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。

　　注意：

　　如果String2在String1中没有找到，instr函数返回0.

　　示例：

　　SELECT instr('syranmo','s') FROM dual; -- 返回 1
　　SELECT instr('syranmo','ra') FROM dual;  -- 返回 3
　　SELECT instr('syran mo','a',1,2) FROM dual;  -- 返回 0

　　（根据条件，由于a只出现一次，第四个参数2，就是说第2次出现a的位置，显然第2次是没有再出现了，所以结果返回0。注意空格也算一个字符！）

　　SELECT instr('syranmo','an',-1,1) FROM dual;  -- 返回 4

　　（就算是由右到左数，索引的位置还是要看‘an’的左边第一个字母的位置，所以这里返回4）

　　SELECT instr('abc','d') FROM dual;  -- 返回 0

　　注：也可利用此函数来检查String1中是否包含String2，如果返回0表示不包含，否则表示包含。



几个我写的代码：

[1].举例

select instr(a.name,',',1,1)+1 AA1,
      instr(a.name,',',1,2)+1 AA2,
      instr(a.name,',',1,3)+1 AA3,
      instr(a.name,',',1,4)+1 AA4,
      instr(a.name,',',1,5)+1 AA5
from(select ',容桂,广珠,西线,管理中心,D8,合胜围,华盛西路,工业区,黄甫大岑置业,一期' as name from dual) a;

AA1 AA2 AA3 AA4 AA5
--- --- --- --- ---
  2   5   8  11  16 

[2].WHERE条件批量模糊匹配。

with keywords_table as(
  select 1 id,'容桂,广珠,西线,管理中心,D8,合胜围,华盛西路,工业区,黄甫大岑置业,一期,14栋,逸湾,剑桥,印象,D7,驾驶学校,科苑二路D8' Name from dual
)
select a.id,
  substr(','||a.name||',',
          instr(','||a.name,',',1,b.rn)+1,
          instr(a.name||',',',',1,b.rn)-instr(','||a.name,',',1,b.rn)
    )name
from keywords_table a,
  (  select rownum rn from dual
     connect by rownum<100
    )b
where length(a.name)-length(replace(a.name,','))+1 >= b.rn

ID NAME                                                                
-- ------------------------
 1 容桂                                                                  
 1 广珠                                                                  
 1 西线                                                                  
 1 管理中心                                                               
 1 D8                                                                    
 1 合胜围                                                                
 1 华盛西路                                                               
 1 工业区                                                                
 1 黄甫大岑置业                                                           
 1 一期                                                                  
 1 14栋                                                                  
 1 逸湾                                                                  
 1 剑桥                                                                  
 1 印象                                                                  
 1 D7                                                                    
 1 驾驶学校                                                               
 1 科苑二路D8                                                             

 选定了 17 行 

select sitename from(
    select siContent.sitename,keyList.name
    from (select sitename from site_info where rownum<=100) siContent,(
        with keywords_table as(
          select 1 id,'容桂,广珠,西线,管理中心,D8,合胜围,华盛西路,工业区,黄甫大岑置业,一期,14栋,逸湾,剑桥,印象,D7,驾驶学校,科苑二路D8' Name from dual
        )
        select a.id,
          substr(','||a.name||',',
                  instr(','||a.name,',',1,b.rn)+1,
                  instr(a.name||',',',',1,b.rn)-instr(','||a.name,',',1,b.rn)
            )name
        from keywords_table a,
          (  select rownum rn from dual
             connect by rownum<100
            )b
        where length(a.name)-length(replace(a.name,','))+1 >= b.rn
    ) keyList
    where siContent.sitename like ('%'||keyList.name||'%')
)group by sitename;

SITENAME
---------------------
顺德容桂华口工业区1
顺德容桂华口2
顺德容桂华口D7
顺德容桂合胜围D7
顺德容桂华口华天东三路2
顺德容桂镇中山黄甫大岑置业D8
顺德容桂东逸湾剑桥印象D8
顺德均安D7
顺德均安南窖D7
顺德均安牛仔城D7
顺德容桂广珠西线管理中心D7
顺德容桂宝供物流3
顺德大良金沙人家一期14栋M1
顺德容桂广珠西高速容桂管理中心1
顺德容桂镇中山黄甫大岑置业2
顺德容桂华口南坊村2
顺德容桂安宁路D9
顺德大良五沙欧洲工业园D7
顺德均安祥安南路D7
顺德容桂华口工业区3
顺德容桂合胜围2
顺德容桂华盛西路D9
顺德容桂桂洲爱德D8
顺德容桂宝供物流1
顺德均安天连D8
顺德均安农行D7
顺德均安兆业制衣D7
顺德容桂广珠西线管理中心D9
顺德容桂华口1
顺德容桂驾驶学校D9
顺德容桂科苑二路D8
顺德容桂桂洲爱德3
顺德容桂安宁路D7
顺德容桂大道碧桂路立交2
顺德容桂宝供物流D8
顺德大良金沙人家一期6栋M1
顺德大良金沙人家一期11栋M1
顺德大良五沙安置区D7
顺德容桂高黎工业区D8
顺德容桂华口D9
顺德容桂驾驶学校D7
顺德均安畅兴工业区D8
顺德均安东堤路D8
顺德均安仓门小学D8
顺德均安畅兴工业区二2
顺德大良金沙人家一期2栋M1
顺德容桂恒基D8
顺德容桂海尾工业区D7
顺德容桂容边隆盛围D7
顺德容桂创富商务公寓A座B1F电井M7
顺德均安沙头D8
顺德均安畅兴心安路D8
顺德大良爱信精机D8
顺德均安畅兴工业区二3
顺德容桂高黎工业区3
顺德容桂华口工业区D6
顺德容桂华口工业区D8
顺德容桂华盛西路D7
顺德容桂海尾工业区D9
顺德容桂容边隆盛围D9
顺德容桂扁滘轻轨站D7
顺德容桂桂洲爱德2
顺德均安畅兴工业区二D8
顺德均安天连大道D8
顺德均安畅兴工业区2

选定了 65 行 

[3].将一行中某列MO_GROUP的多个MO拆分成多行。有几个就拆分成几行。

execute immediate 'insert into cy_runtime_log(ID,PROCEDURE_NAME)values(cy_runtime_log_SEQ.nextval,''cy_three_cg_bpc_diptg_result begin'')'; commit;
insert into cy_three_cg_bpc_diptg_result(TTIME,TG,BSC,MO,所需64K传输数,在用64K信道数,所需16K传输数,在用16K传输数)
with keywords_table as(select rownum as id,MO as NAME,length(MO)-length(replace(MO,',')) as MO_LEN,
         TTIME,TG,BSC,MO,所需64K传输数,在用64K信道数,所需16K传输数,在用16K传输数
  from cy_bpc_cg_dip_tg
  where tg in ('RXOTG-200','RXOTG-98')
)
select --a.id,
      a.TTIME,a.TG,a.BSC,
      substr(','||a.name||',',
            instr(','||a.name,',',1,b.rn)+1,
            instr(a.name||',',',',1,b.rn)-instr(','||a.name,',',1,b.rn)
      )name,
      a.所需64K传输数,a.在用64K信道数,a.所需16K传输数,a.在用16K传输数
from keywords_table a,
  (  select rownum rn from dual
     connect by rownum<100
    )b
where MO_LEN >= b.rn;