SQL> create or replace function getcol(mystr char,seg_no integer) return char is
2 Result char(100);
3 i integer;
4 rl integer;
5 mylen integer;
6 mytag char(2);
7 mytemp char(100);--left string seg
8 begin
9 mytemp := MyStr;
10 mytag := '@@';
11 mylen := length(trim(mytemp));
12 i := seg_no;
13
14 while (i > 0) loop
15
16 rl := instr(mytemp,mytag) - 1;
17
18 if rl = -1 then
19 result := mytemp;
20 exit;
21 end if;
22
23 result := substr(mytemp, 1, rl);
24 mytemp := substr(mytemp, rl + 3,mylen - rl);
25
26 I := I - 1;
27 end loop;
28
29 return(Result);
30 end getcol;
31 /函数已创建。SQL> select getcol('WW@@3467.213@@[email protected]',2) 第二段字符 from dual;第二段字符
--------------------------------------------------------------------------------
3467.213
SQL> select getcol('WW@@3467.213@@[email protected]',3) no3 from dual;NO3
--------------------------------------------------------------------------------
[email protected]
SQL> select getcol('WW@@3467.213@@[email protected]',1) no3 from dual;NO3
--------------------------------------------------------------------------------
WW
SQL> select getcol('WW@@3467.213@@[email protected]',0) no0 from dual;NO0
--------------------------------------------------------------------------------
2 Result char(100);
3 i integer;
4 rl integer;
5 mylen integer;
6 mytag char(2);
7 mytemp char(100);--left string seg
8 begin
9 mytemp := MyStr;
10 mytag := '@@';
11 mylen := length(trim(mytemp));
12 i := seg_no;
13
14 while (i > 0) loop
15
16 rl := instr(mytemp,mytag) - 1;
17
18 if rl = -1 then
19 result := mytemp;
20 exit;
21 end if;
22
23 result := substr(mytemp, 1, rl);
24 mytemp := substr(mytemp, rl + 3,mylen - rl);
25
26 I := I - 1;
27 end loop;
28
29 return(Result);
30 end getcol;
31 /函数已创建。SQL> select getcol('WW@@3467.213@@[email protected]',2) 第二段字符 from dual;第二段字符
--------------------------------------------------------------------------------
3467.213
SQL> select getcol('WW@@3467.213@@[email protected]',3) no3 from dual;NO3
--------------------------------------------------------------------------------
[email protected]
SQL> select getcol('WW@@3467.213@@[email protected]',1) no3 from dual;NO3
--------------------------------------------------------------------------------
WW
SQL> select getcol('WW@@3467.213@@[email protected]',0) no0 from dual;NO0
--------------------------------------------------------------------------------
-------------------
create or replace function getcol(mystr char,seg_no integer) return char is
Result char(100);
i integer;
rl integer;
mylen integer;
mytag char(2);
mytemp char(100);--left string seg
begin
mytemp := MyStr;
mytag := '@@';
mylen := length(trim(mytemp));
i := seg_no;
while (i > 0) loop
rl := instr(mytemp,mytag) - 1;
if rl = -1 then
result := mytemp;
exit;
end if;
result := substr(mytemp, 1, rl);
mytemp := substr(mytemp, rl + 3,mylen - rl);
I := I - 1;
end loop;
return(Result);
end getcol;===============
下班也……
++++++++++++++++++++++create or replace function getcol(mystr char,mytag char,seg_no integer) return char is
Result char(100);
i integer;
rl integer;
mylen integer;
--mytag char(2);
mytemp char(100);--left string seg
begin
mytemp := MyStr;
--mytag := '@@';
mylen := length(trim(mytemp));
i := seg_no;
while (i > 0) loop
rl := instr(mytemp,mytag) - 1;
if rl = -1 then
result := mytemp;
exit;
end if;
result := substr(mytemp, 1, rl);
mytemp := substr(mytemp, rl + 3,mylen - rl);
I := I - 1;
end loop;
return(Result);
end getcol;
2 v_str varchar2(50);
3 begin
4 v_str:='123@@12.45@@248';
5 while instr(v_str,'@@')>0 loop
6 dbms_output.put_line(substr(v_str,1,instr(v_str,'@@')-1));
7 v_str:=substr(v_str,instr(v_str,'@@')+2);
8 end loop;
9 dbms_output.put_line(v_str);
10 end;
11 /
123
12.45
248PL/SQL 过程已成功完成。SQL>
CREATE OR REPLACE FUNCTION "M_SPLIT" (m_c VARCHAR2,s_c VARCHAR2)
RETURN m_array IS
--本函数实现了对字符串的split功能
--m_c,需要分割的字符串;s_c分割符
--定义变量
--type m_array is varray(20) of varchar2(200);--定义返回数组类型
v_m m_array := m_array('');--初始化数组
array_i number;--数组下标
t_c VARCHAR2(1000);
BEGIN
array_i := 1;
t_c := m_c;
while instr(t_c,s_c,1) > 0 Loop
if array_i > 1 then
v_m.extend;
end if;
t_c := trim(t_c);
--dbms_output.put_line(substr(m_c,0,instr(m_c,s_c,1)-1));
v_m(array_i) := substr(t_c,0,instr(t_c,s_c,1)-1);
t_c := substr(t_c,instr(t_c,s_c,1)+2);
--dbms_output.put_line(m_c);
array_i := array_i+1;
end Loop;
return v_m;
END M_SPLIT;