oracle的In传入字符串1;2;3;4;5怎么拼接成('1','2','3','4')

[gxh04007101]

oracle的In传入字符串1;2;3;4;5怎么拼接成('1','2','3','4')供in 做查询使用.请大神帮忙!谢谢！

[gxh04007101]

SELECT * FROM EMP E WHERE E.ENAME IN(SELECT CONCAT(CONCAT('''',REPLACE('10001;00026;00025',';',''',''')),'''') FROM DUAL) 这样写不起作用!

[forgetsam]

当然不起作用。in (?) 和 in (?,?,?,?,?) 怎么也不可能一样。 SELECT * FROM EMP E WHERE E.ENAME IN(select regexp_substr('10001;00026;00025','\d+',1,level) from dual connect by level <=3)

[TUBER727]

SQL> DECLARE 2 v_str varchar2(1000); 3 v_str1 varchar2(1000); 4 --v_str2 varchar2(1000); 5 --v_sql varchar(1000); 6 begin 7 --v_str_1='''; 8 --v_str_2=''''; 9 v_str := '1;2;3;4;5'; 10 v_str1 := replace(v_str,';',chr(39)||chr(44)||chr(39)); 11 v_str := '('||''''||v_str1||''''||')'; 12 dbms_output.put_line(v_str); 13 end; 14 / ('1','2','3','4','5') PL/SQL procedure successfully completed

[hldwb]

create or replace function strsplit(p_value varchar2, p_split varchar2 := ',') --usage: select * from table(strsplit('1,2,3,4,5')) return strsplit_type pipelined is v_idx integer; v_str varchar2(500); v_strs_last varchar2(4000) := p_value; begin loop v_idx := instr(v_strs_last, p_split); exit when v_idx = 0; v_str := substr(v_strs_last, 1, v_idx - 1); v_strs_last := substr(v_strs_last, v_idx + 1); pipe row(v_str); end loop; pipe row(v_strs_last); return; end strsplit; SELECT * FROM EMP E WHERE E.ENAME IN (select * from table(strsplit('1;2;3;4;5',';')))

[DRAGON__90]

SELECT ''''||regexp_replace('1;2;3;4;5','\W',''',''')||'''' FROM dual;

[jiekou0704]

自己写个处理函数就ok