我有一函数可以实现: CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_str_split IS j INT := 0; i INT := 1; len INT := 0; len1 INT := 0; str VARCHAR2 (4000); str_split ty_str_split := ty_str_split (); BEGIN len := LENGTH (p_str); len1 := LENGTH (p_delimiter); WHILE j < len LOOP j := INSTR (p_str, p_delimiter, i); IF j = 0 THEN j := len; str := SUBSTR (p_str, i); str_split.EXTEND; str_split (str_split.COUNT) := str; IF i >= len THEN EXIT; END IF; ELSE str := SUBSTR (p_str, i, j - i); i := j + len1; str_split.EXTEND; str_split (str_split.COUNT) := str; END IF; END LOOP; RETURN str_split; END fn_split; /测试: select * from table (fn_split ('1,2,3', ','));
To get more knowledge will be more important than others. :)For this issue, you can consult following link also:http://topic.csdn.net/u/20080116/16/2b1ce5e0-2e20-4b63-9bae-3f49e0eba1ca.html
比如
insert into table(colum)values(1);
insert into table(colum)values(2);
insert into table(colum)values(3);
insert into table(colum)values(4);
insert into table(colum)values(5); 请问如何做?是否要用拆分函数,然后用动态sql?--------------------------------
select sname from test;SNAME
---------
1,2,3,4,5SQL>
SQL> select substr(','||t1.sname||',',
2 instr(','||t1.sname||',',',',1,rn)+1,
3 instr(','||t1.sname||',',',',1,rn+1)-instr(','||t1.sname||',',',',1,rn)-1) as new_name
4 from test t1,
5 (
6 select rownum rn
7 from all_objects
8 where rownum <= 10
9 )t2
10 where instr(','||t1.sname||',',',',1,rn+1) > 0;NEW_NAME
-----------
1
2
3
4
5
我以前写过个单字符串的拆分
SELECT DECODE (INSTR (:in_cid, ',', 1, 1),
0, :in_cid,
SUBSTR (:in_cid,
DECODE (ROWNUM,
1, 1,
INSTR (:in_cid, ',', 1, ROWNUM - 1) + 1
),
DECODE (ROWNUM,
1, INSTR (:in_cid, ',', 1, 1) - 1,
LENGTH (:in_cid)
- LENGTH (REPLACE (:in_cid, ',', ''))
+ 1, LENGTH (:in_cid),
INSTR (:in_cid, ',', 1, ROWNUM)
- 1
- INSTR (:in_cid, ',', 1, ROWNUM - 1)
)
)
) cid
FROM DUAL
CONNECT BY ROWNUM <= LENGTH (:in_cid) - LENGTH (REPLACE (:in_cid, ',', ''))
+ 1多个字段的字符串拆分,今天看到楼上写的,佩服啊
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN ty_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
str_split ty_str_split := ty_str_split ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter); WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i); IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
str_split.EXTEND;
str_split (str_split.COUNT) := str; IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
str_split.EXTEND;
str_split (str_split.COUNT) := str;
END IF;
END LOOP; RETURN str_split;
END fn_split;
/测试:
select * from table (fn_split ('1,2,3', ','));