select a from ( select substr('a,b,c,d,e',rownum,1) a from dual CONNECT BY rownum<=length( 'a,b,c,d,e') ) where a<>','--result: a b c d e
字符串解析函数,算法可以参考,不知你的需求要求返回的是什么类型....还是直接显示 CREATE OR REPLACE FUNCTION V_splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN str_split PIPELINED AS v_length NUMBER := LENGTH(p_string); v_start NUMBER := 1; v_index NUMBER; BEGIN WHILE(v_start <= v_length) LOOP v_index := INSTR(p_string, p_delimiter, v_start); IF v_index = 0 THEN PIPE ROW(SUBSTR(p_string, v_start)); v_start := v_length + 1; ELSE PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start)); v_start := v_index + 1; END IF; END LOOP; RETURN; END V_splitstr;
我要返回字符串类型,应该是多个值才对吧。我不会调用,可以出个例子吗。本人愚笨 假使一个表: create table T_a (id number(7) primary key,value1 varchar2(20)); insert into t_a values(1,'a,b,c,d'); insert into t_b values(2,'3d,43,se,iw'); commit; 现在我想取出数据 该怎么查询呢? 1 a b c d 这样子分别取出。 谢谢。
declare i int := -1; aaa varchar2; begin while i <=length(drscode) loop select t.drscode,substr(t.drscode,instr(t.drscode,',',1)+i,instr(t.drscode,',',1,2)-instr(t.drscode,',',1)-1) into aaa from temp t dbms_output.put_line(aaa); i := i + 2; end loop;
我想用 该 对应的 sql 语句。不能用java。另外 本人 Oracle数据库实在菜。望道明。!!非常感谢。
select a from
(
select substr('a,b,c,d,e',rownum,1) a from dual
CONNECT BY rownum<=length( 'a,b,c,d,e')
) where a<>','--result:
a
b
c
d
e
CREATE OR REPLACE FUNCTION V_splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split
PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start); IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP; RETURN;
END V_splitstr;
我要返回字符串类型,应该是多个值才对吧。我不会调用,可以出个例子吗。本人愚笨
假使一个表:
create table T_a (id number(7) primary key,value1 varchar2(20));
insert into t_a values(1,'a,b,c,d');
insert into t_b values(2,'3d,43,se,iw');
commit;
现在我想取出数据 该怎么查询呢? 1 a b c d 这样子分别取出。 谢谢。
select number,substr(value1, 1, instr(value1, ',', 1, 1) - 1) b1,
substr(value1,
instr(value1, ',', 1, 1) + 1,
instr(value1, ',', 1, 2) - instr(value1, ',', 1, 1) - 1) b2,
substr(value1,
instr(value1, ',', 1, 2) + 1,
instr(value1, ',', 1, 3) - instr(value1, ',', 1, 2) - 1) b3,
substr(value1,
instr(value1, ',', 1, 3) + 1,
length(value1) - instr(value1, ',', 1, 3)) b5
from t_a;
i int := -1;
aaa varchar2;
begin
while i <=length(drscode) loop
select t.drscode,substr(t.drscode,instr(t.drscode,',',1)+i,instr(t.drscode,',',1,2)-instr(t.drscode,',',1)-1) into aaa from temp t
dbms_output.put_line(aaa);
i := i + 2;
end loop;