以前有写过相关的代码,但是没有系统化。 计算目标字符串在源串中出现的次数 create or replace function str_cnt(aa varchar2,bb varchar2) return number is num number(10):=0; begin for i in 1..length(aa) loop if instr(aa,bb,1,i)>0 then num:=num+1; else return num; end if; end loop; return num; end; /SQL> select str_cnt('a,大海和大哥bd','大') from dual;STR_CNT('A,大海和大哥BD','大') ------------------------------ 2
将分组连接的字符串打散 create table qc_str(aa number(10),bb varchar(64))tablespace bidm_data; insert into qc_str values(1,'aa,bb,cc,dd'); insert into qc_str values(2,'c,d,e,f,g,h,i');select a.aa,substr(bb,instr(a.bb,',',1,b.num-1)+1,instr(a.bb,',',1,b.num)-instr(a.bb,',',1,b.num-1)-1) from (select aa,','||ltrim(rtrim(bb,','),',')||',' bb from qc_str ) a,(select rownum+1 num from dual connect by rownum<=(select max(length(bb)-length(replace(bb,',',''))) num from qc_str)) b where length(bb)-length(replace(bb,',',''))>=b.num order by 1,2 ; AA SUBSTR(BB,INSTR(A.BB,',',1,B.NUM-1)+1,INSTR(A.BB,',',1,B.NUM)-INSTR(A.BB,',',1,B.NUM-1)-1) ---------- ------------------------------------------------------------------------------------------------------------------------------------ 1 aa 1 bb 1 cc 1 dd 2 c 2 d 2 e 2 f 2 g 2 h 2 i
10 rows selected.
with tt as(select '0001001'claimno,'17,15,6' ,'2009-9-10' checkdate from dual union all select '0001002','1,2,3,4','2009-10-1' from dual)select claimno, substr(','||tt.,instr(','||tt.,',',1,temp.rn)+1,instr(tt.||',',',',1,temp.rn)-instr(','||tt.,',',1,temp.rn)), checkdate from tt, (select rownum rn from dual connect by rownum<10)temp where length(tt.)-length(replace(tt.,','))+1>=temp.rn order by 1,temp.rnCLAIMNO MARK CHECKDATE 0001001 17 2009-9-10 0001001 15 2009-9-10 0001001 6 2009-9-10 0001002 1 2009-10-1 0001002 2 2009-10-1 0001002 3 2009-10-1 0001002 4 2009-10-1rownum<10中的10根据实际需要更改。要大于中可能有的最大项数。比如这个例子中,0001002中的字符串数最多,有4项,则rownum<后应设为不小于5的数
CREATE OR REPLACE FUNCTION F_STR_SPLIT(I_STR VARCHAR2, I_SPLIT VARCHAR2, I_CHECKDUP CHAR) RETURN T_SUBSTR IS /************* 前提:I_STR 源字符串, I_SPLIT 分割标识 不能为空 I_CHECKDUP 是否校验存在重复的子字符串 功能:字符串分割函数 时间:2009-3-7 *************/ V_SUBSTR T_SUBSTR; -- 返回结果 V_LENG_SPLIT NUMBER; --分割标识长度 V_I NUMBER; -- 循环标识 V_J NUMBER; --循环标识 V_STR_POINT INTEGER; --源字符串移动指针 V_SPLIT_POINT INTEGER; --分割标识移动指针BEGIN IF I_STR IS NULL THEN RETURN NULL; ELSIF I_SPLIT IS NULL THEN RETURN T_SUBSTR(I_STR); END IF; V_I := 1; V_LENG_SPLIT := LENGTH(I_SPLIT); V_SUBSTR := T_SUBSTR(); V_STR_POINT := 1; LOOP V_SPLIT_POINT := INSTR(I_STR, I_SPLIT, V_STR_POINT); V_SUBSTR.EXTEND; IF V_SPLIT_POINT <> 0 THEN V_SUBSTR(V_I) := SUBSTR(I_STR, V_STR_POINT, V_SPLIT_POINT - V_STR_POINT); ELSE V_SUBSTR(V_I) := SUBSTR(I_STR, V_STR_POINT); EXIT; END IF; IF I_CHECKDUP = '1' THEN -- check duplication value FOR V_J IN 1 .. V_I LOOP IF V_J <> V_I AND V_SUBSTR(V_J) = V_SUBSTR(V_I) THEN RAISE_APPLICATION_ERROR(-20000, 'Duplicate value on ' || V_J || ' and ' || V_I || ' :' || V_SUBSTR(V_I)); END IF; END LOOP; END IF; V_STR_POINT := V_SPLIT_POINT + V_LENG_SPLIT; V_I := V_I + 1; END LOOP; RETURN(V_SUBSTR); END F_STR_SPLIT;
应该有规律的吧。如果全部数据都是在最后有个逗号 稍微进行下处理就行了 with tt as(select '0001001'claimno,'17,15,6,' ,'2009-9-10' checkdate from dual union all select '0001002','1,2,3,4,','2009-10-1' from dual)select claimno, substr(','||tt.,instr(','||tt.,',',1,temp.rn)+1,instr(tt.,',',1,temp.rn)-instr(','||tt.,',',1,temp.rn)), checkdate from tt, (select rownum rn from dual connect by rownum<10)temp where length(tt.)-length(replace(tt.,','))>=temp.rn order by 1,temp.rn
计算目标字符串在源串中出现的次数
create or replace function str_cnt(aa varchar2,bb varchar2)
return number
is
num number(10):=0;
begin
for i in 1..length(aa) loop
if instr(aa,bb,1,i)>0 then
num:=num+1;
else
return num;
end if;
end loop;
return num;
end;
/SQL> select str_cnt('a,大海和大哥bd','大') from dual;STR_CNT('A,大海和大哥BD','大')
------------------------------
2
将分组连接的字符串打散
create table qc_str(aa number(10),bb varchar(64))tablespace bidm_data;
insert into qc_str values(1,'aa,bb,cc,dd');
insert into qc_str values(2,'c,d,e,f,g,h,i');select a.aa,substr(bb,instr(a.bb,',',1,b.num-1)+1,instr(a.bb,',',1,b.num)-instr(a.bb,',',1,b.num-1)-1)
from
(select aa,','||ltrim(rtrim(bb,','),',')||',' bb from qc_str ) a,(select rownum+1 num from dual connect by rownum<=(select max(length(bb)-length(replace(bb,',',''))) num from qc_str)) b
where length(bb)-length(replace(bb,',',''))>=b.num
order by 1,2
;
AA SUBSTR(BB,INSTR(A.BB,',',1,B.NUM-1)+1,INSTR(A.BB,',',1,B.NUM)-INSTR(A.BB,',',1,B.NUM-1)-1)
---------- ------------------------------------------------------------------------------------------------------------------------------------
1 aa
1 bb
1 cc
1 dd
2 c
2 d
2 e
2 f
2 g
2 h
2 i
10 rows selected.
union all select '0001002','1,2,3,4','2009-10-1' from dual)select claimno,
substr(','||tt.,instr(','||tt.,',',1,temp.rn)+1,instr(tt.||',',',',1,temp.rn)-instr(','||tt.,',',1,temp.rn)),
checkdate
from tt,
(select rownum rn from dual connect by rownum<10)temp
where length(tt.)-length(replace(tt.,','))+1>=temp.rn
order by 1,temp.rnCLAIMNO MARK CHECKDATE
0001001 17 2009-9-10
0001001 15 2009-9-10
0001001 6 2009-9-10
0001002 1 2009-10-1
0001002 2 2009-10-1
0001002 3 2009-10-1
0001002 4 2009-10-1rownum<10中的10根据实际需要更改。要大于中可能有的最大项数。比如这个例子中,0001002中的字符串数最多,有4项,则rownum<后应设为不小于5的数
I_SPLIT VARCHAR2,
I_CHECKDUP CHAR) RETURN T_SUBSTR IS /*************
前提:I_STR 源字符串,
I_SPLIT 分割标识 不能为空
I_CHECKDUP 是否校验存在重复的子字符串 功能:字符串分割函数
时间:2009-3-7
*************/
V_SUBSTR T_SUBSTR; -- 返回结果
V_LENG_SPLIT NUMBER; --分割标识长度
V_I NUMBER; -- 循环标识
V_J NUMBER; --循环标识
V_STR_POINT INTEGER; --源字符串移动指针
V_SPLIT_POINT INTEGER; --分割标识移动指针BEGIN
IF I_STR IS NULL THEN
RETURN NULL;
ELSIF I_SPLIT IS NULL THEN
RETURN T_SUBSTR(I_STR);
END IF;
V_I := 1; V_LENG_SPLIT := LENGTH(I_SPLIT); V_SUBSTR := T_SUBSTR();
V_STR_POINT := 1;
LOOP
V_SPLIT_POINT := INSTR(I_STR, I_SPLIT, V_STR_POINT);
V_SUBSTR.EXTEND;
IF V_SPLIT_POINT <> 0 THEN
V_SUBSTR(V_I) := SUBSTR(I_STR,
V_STR_POINT,
V_SPLIT_POINT - V_STR_POINT);
ELSE
V_SUBSTR(V_I) := SUBSTR(I_STR, V_STR_POINT);
EXIT;
END IF; IF I_CHECKDUP = '1' THEN
-- check duplication value
FOR V_J IN 1 .. V_I LOOP
IF V_J <> V_I AND V_SUBSTR(V_J) = V_SUBSTR(V_I) THEN
RAISE_APPLICATION_ERROR(-20000,
'Duplicate value on ' || V_J || ' and ' || V_I || ' :' ||
V_SUBSTR(V_I));
END IF;
END LOOP;
END IF; V_STR_POINT := V_SPLIT_POINT + V_LENG_SPLIT;
V_I := V_I + 1;
END LOOP; RETURN(V_SUBSTR);
END F_STR_SPLIT;
稍微进行下处理就行了
with tt as(select '0001001'claimno,'17,15,6,' ,'2009-9-10' checkdate from dual
union all select '0001002','1,2,3,4,','2009-10-1' from dual)select claimno,
substr(','||tt.,instr(','||tt.,',',1,temp.rn)+1,instr(tt.,',',1,temp.rn)-instr(','||tt.,',',1,temp.rn)),
checkdate
from tt,
(select rownum rn from dual connect by rownum<10)temp
where length(tt.)-length(replace(tt.,','))>=temp.rn
order by 1,temp.rn