17,078
社区成员
发帖
与我相关
我的任务
分享
SQL> create or replace procedure checkStr(inputStr varchar2,appearCount in num
r,returnStr out varchar2)
2 as
3 begin
4 if appearCount=1 then
5 select substr(inputStr,0,instr(inputstr,';',1,1)-1) into ret
nStr from dual;
6 else
7 dbms_output.put_line(instr(inputStr,';',1,appearCount-1));
8 dbms_output.put_line(instr(inputStr,';',1,appearCount));
9 select substr(inputStr,instr(inputStr,';',1,appearCount-1)+1
nstr(inputStr,';',1,appearCount)-instr(inputStr,';',1,appearCount-1)-1) into r
urnStr from dual;
10 end if;
11 end;
12 /
过程已创建。
SQL> declare
2 rstr varchar2(20);
3 begin
4 checkStr('11;22;3323;abc;ddd',4,rstr);
5 dbms_output.put_line(rstr);
6 end;
7 /
11
15
abc
PL/SQL 过程已成功完成。
SQL> declare
2 rstr varchar2(20);
3 begin
4 checkStr('11;22;3323;abc;ddd',3,rstr);
5 dbms_output.put_line(rstr);
6 end;
7 /
6
11
3323
PL/SQL 过程已成功完成。
SQL>
--通用一点,可指定分隔符
CREATE OR REPLACE FUNCTION getstr(str VARCHAR2, n PLS_INTEGER, delim CHAR) RETURN VARCHAR2 IS
tempstr VARCHAR2(2000);
--str :原始字符串
--n :取第几个字符串
--delim:分隔符
--调用方式: getstr('11;22;3323;abc;ddd',3,';')
BEGIN
tempstr := delim || str || delim;
RETURN substr(tempstr,
instr(tempstr, delim, 1, n) + 1,
instr(tempstr, delim, 1, n + 1) - instr(tempstr, delim, 1, n) - 1);
END;
/
SQL> edi
已写入 file afiedt.buf
1 create or replace function fun_zf(str varchar2,i number) return varchar2
2 as
3 id varchar2(100);
4 str2 varchar2(100);
5 begin
6 id:=';'||str||';';
7 select
8 substr(id,instr(id,';',1,i)+1,instr(id,';',1,i+1)-instr(id,';',1,i)-1) into str2
9 from dual;
10 return str2;
11* end;
SQL> /
函数已创建。
SQL> select fun_zf('11;22;3323;abc;ddd',1) from dual
2 /
FUN_ZF('11;22;3323;ABC;DDD',1)
--------------------------------------------------------------------------------
11
SQL> select fun_zf('11;22;3323;abc;ddd',4) from dual
2 /
FUN_ZF('11;22;3323;ABC;DDD',4)
--------------------------------------------------------------------------------
abc