[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-09
8
Medium Priority
?
641 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:GouthamAnand
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 23592249
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
 

Author Comment

by:GouthamAnand
ID: 23592761
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
 
LVL 48

Expert Comment

by:schwertner
ID: 23592983
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 23592998
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
 

Author Closing Comment

by:GouthamAnand
ID: 31544616
Thank you very much.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23593170
glad I could help
0
 
LVL 6

Expert Comment

by:Ritesh_Garg
ID: 23593479
You may write your query as:


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

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23593914
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question