how to fetch the values from a string separated by commas in Oracle pl/sql

build the attached type and function first...


then write your query like this....


select * from emp where emp name in (select * from table(str2tbl(v_values)))

CREATE OR REPLACE TYPE  VCARRAY AS TABLE OF VARCHAR2(4000);
 
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray 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 str2tbl;

Select allOpen in new window

Sorry I did not mention that I am using Oracle 8i. I think this work from Oracle 9i onwards. Can you please let me know the solution in oracle 8i.

create or replace type varchar2_tab as table of varchar2(30)
/

CREATE OR REPLACE FUNCTION str2tbl(
    p_string    IN VARCHAR2,
    p_delimiter IN VARCHAR2 DEFAULT ','
)
RETURN varchar2_tab
AS
     l_string   LONG DEFAULT p_string || p_delimiter;
     l_index    INTEGER;
     l_table    varchar2_tab := varchar2_tab();
BEGIN
    IF (p_string IS NULL)
    THEN
        RETURN l_table;
    END IF;

    IF (p_delimiter IS NULL)
    THEN
        FOR i IN 1..length(l_string)
        LOOP
            l_table.extend;
            l_table(l_table.count) := trim(substr(l_string,i,1));
        END LOOP;
        RETURN l_table;
    END IF;
       
    LOOP
        l_index := instr(l_string,p_delimiter);
        exit when (nvl(l_index,0) = 0);
        l_table.extend;
        l_table(l_table.count) := trim(substr(l_string,1,l_index-1));
        l_string := substr(l_string,l_index+length(p_delimiter));
    END LOOP;
    RETURN l_table;
END str2tbl;
/


Now you can use the code:

select * from table(str2tbl('1,2,3,4,5,5,6,7'));

select * from table(str2tbl('phr_,bas_,ehf_'));

Thank you very much.

glad I could help

You may write your query as:

Select * from emp
where instr('arun,kiran,raj', empname || ',') > 0

Select allOpen in new window

the INSTR method only works if the string to be searched ends in ','  and if the substring has no commas (which may be a good assumption, but it's not universally true)

it also prevents any indexes on empname from being used.