Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Red face Unanswered: Function to split comma delimited values

    I need to be able to use a function to split a set of varchar values in a stored procedure.

    For example, I need it to do something like this:


    a:= '111,222,333,444,555'
    b:= 'AAA,BBB,CCC,DDD, EEE'

    create procedure test (a in varchar2, b in varchar2)
    as
    select id from reservations
    where customer in (select value from somefunction(a))
    union
    select id from reservations
    where room in (select value from somefunction(b))

    I'm sure the syntax above is not close but its just to give an idea of what I'm asking. All the examples I've seen are functions designed to be used in an execute immediate situation but I'd like to avoid the use of execute immediate in my stored procedure.

    Thanks!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >b:= 'AAA,BBB,CCC,DDD, EEE'
    The above is NOT valid syntax to be used in a WHERE clause.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  4. #4
    Join Date
    Jul 2004
    Posts
    53
    Sorry I should have clarified: I know the syntax is not gonna be right but what I would like to have happen is to pass b into a stored procedure looking like this:

    b := 'AAA,BBB,CCC,DDD, EEE'

    I want to know if there is a function that will take b

    and turn it into this:

    b:= 'AAA', 'BBB', 'CCC', 'DDD', 'EEE'

    so I can do this:

    select id from reservations
    where room in ('AAA', 'BBB', 'CCC', 'DDD', 'EEE')

  5. #5
    Join Date
    Jul 2004
    Posts
    53
    shoblock I'll check out that link...thanks!

  6. #6
    Join Date
    Jul 2004
    Posts
    53
    Awesome! It worked! Helped by shoblock yet again Muchas gracias

  7. #7
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Another option is to use INSTR.

    For example, the code

    HTML Code:
    select id from reservations 
    where customer in (select value from somefunction(a))
    does the same as
    HTML Code:
    select id from reservations
    where INSTR(b, customer) > 0
    That is a very simple approach.

    Hope that helps.

    Ravi

  8. #8
    Join Date
    Jul 2004
    Posts
    53
    Had no clue that instr works on columns! One of the benefits of being a newbie..everything you find gets you excited...

    I tried it and it works for what I need and its simpler...I'm happy

    I thought about the problem of running into a scenario where I have a varchar in another varchar value:

    i.e

    a:= '111, 222, 333, 1114'

    using Instr on '111' will return both records for 111 and 1114.

    So I searched on the web about this instr function...I found this great link which is helpful for cases like this and for cases when you want to exclude values in this format.

    http://www.quest-pipelines.com/newsletter-v3/0202_B.htm

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Quote Originally Posted by awfdml
    Had no clue that instr works on columns! One of the benefits of ....
    [observation]
    And one of the problems of 'modern' programmers being brought up on GUI interfaces etc... no understanding of Kernighan and Ritchie's work - (more specifically the principles of a recursive descendant parser on which many modern languages were founded).
    [/observation]

    http://www.google.com/search?hl=en&q...=Google+Search
    http://www.google.com/search?hl=en&q...itchie&spell=1

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  10. #10
    Join Date
    Jul 2004
    Posts
    53
    I have a dilemma related to this:

    I need to pass in a string holding the id numbers for items into a stored procedure. I am also passing in a corresponding string that tells me the quantity requested for each item.

    Just to give you an idea:

    items := 'a100, a101, a102'
    qty := '7, 3, 2'

    So I've requested 7 of item#: a100, 3 of item#: a101, 2 of item#:a102.

    I would like to be able to go through each of the lists and do an insert into either an array or a table.


    1 a100 7
    2 a101 3
    3 a102 2


    The solution on the link from the asktom website would work great except for the restrictions I have. I am not allowed to create any types whatsoever (i.e. create table...create varray). DBA's rules...

    I can declare table types but those I know are only 1 dimensional. I'm searching for information on multi-dimensional arrays but I cant find anything that's useful...

    id appreciate any help....thanks
    Last edited by awfdml; 10-14-04 at 19:43. Reason: messed up example

  11. #11
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Here's one way to make it work.

    Write a function that takes a string, a separator and returns a PL/SQL array.

    (Please note that this is just for illustrative purposes. The syntax may be incorrect.)
    PHP Code:
       DECLARE
          
    Type pl_tab is table of varchar2(100index by binary_integer;
           
    qty_arr pl_tab;
           
    item_arr  pl_tab;
       
           -- 
    defined elsewhere
         
    -- function string_to_array(p_str in varchar2p_sep in varchar2)
            return 
    pl_tab;
          -- 
        
    BEGIN
             item_arr 
    string_to_array(items',');
             
    qty_arr  string_to_arr(qty',');
       
             FOR 
    i in 1 .. item_arr.count
             LOOP
                 insert into table_x values 
    (item_arr(i), qty_arr(i), ...);
             
    END LOOP;
        
    END
    Depending on your version of Oracle, you may be able to use the "BULK" loading features. But this gives you the idea of how to proceed.


    Hope that helps.

  12. #12
    Join Date
    Apr 2004
    Posts
    246
    personally I prefer to just use instr, or better yet, don't write systems where a list of values is passed as one concatenated string. anyway, to avoid finding "partial" values, put extra delimiters all over the place:

    INSTR( ',' || customer || ',' , ',' || b || ',' ) > 0

    if the list string (b) may contain leading or trailing commas, then trim them off first: instr( ... || ltrim(rtrim(b,','),',') || ...
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •