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

I need to load the values arun, kiran and raj in a table also can you please let me know how can I use this string in IN clause of an sql?
like select * from emp where emp name in (v_values)
somthing like this?
v_values varchar2(100):='arun,kiran,raj';

Open in new window

GouthamAnandAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
ok,  you can still take the same approach, it's slightly more complicated syntax though...

create the type as above but create the function with the attached code and query like this...

select * from table(cast(str2tbl(v_values) as vcarray))
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
    RETURN vcarray
AS
    v_list   vcarray := vcarray(NULL);
    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_start > 1
        THEN
            v_list.EXTEND;
        END IF;
 
        IF v_index = 0
        THEN
            v_list(v_list.LAST) := SUBSTR(p_string, v_start);
            v_start := v_length + 1;
        ELSE
            v_list(v_list.LAST) := SUBSTR(p_string, v_start, v_index - v_start);
 
            v_start := v_index + 1;
        END IF;
    END LOOP;
 
    RETURN v_list;
END str2tbl;

Open in new window

0
 
sdstuberCommented:
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;

Open in new window

0
 
GouthamAnandAuthor Commented:
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
schwertnerCommented:
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_'));
0
 
GouthamAnandAuthor Commented:
Thank you very much.
0
 
sdstuberCommented:
glad I could help
0
 
Ritesh_GargCommented:
You may write your query as:


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

Open in new window

0
 
sdstuberCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.