PostgreSQL (Postgre Plus Advanced Server) 에 맞게 수정해 보았습니다.
참고하세요..
(copy_t 테이블은 1~20000 정수가 들어있는 테이블입니다.)
v_list := '111,222,333,444,555,';
SELECT *
FROM tb_c99_docinfo
WHERE docid IN (
select substr(txt, s_no, (e_no-s_no)+1))::integer num
from (
select t2.no, t2.no-1 lvl, t1.len, t1.txt,
case when t2.no-1>0 then instr (txt, ','::text, 1, t2.no-1) + 1 else 1 end s_no,
instr (txt, ','::text, 1, t2.no)-1 e_no
from (
select v_list txt, ( length (v_list) ) - length (replace (v_list, ',', '')) + 1 len from dual a
) t1, copy_t t2
where t2.no<=t1.len
)
where no )
;
<오라클버젼>
참고: http://database.sarang.net/?inc=read&aid=35277&criteria=oracle&subcrit=&id=&limit=20&keyword=%B1%B8%BA%D0%C0%DA&page=1
SELECT *
FROM tb_c99_docinfo
WHERE docid IN (
SELECT TRIM( SUBSTR (txt, INSTR (txt, ',', 1, LEVEL) + 1, INSTR (txt, ',', 1, LEVEL + 1) - INSTR (txt, ',', 1, LEVEL) - 1) ) AS token
FROM (
SELECT ',' || docattach || ',' txt
FROM tb_c99_docinfo a
WHERE docid = 125
)
CONNECT BY LEVEL <= (LENGTH (txt) - 2) - LENGTH (REPLACE (txt, ',', '')) + 1
);
|
