How to get value from comma spearated string in the loop?

See the Code Below:

DECLARE @NodesList varchar(4000)
DECLARE @NodeName varchar(10)
Declare @Pos int

 SET @NodesList = @Replace_This_With_Your_Parameter

 SET @Pos = CHARINDEX(',', @NodesList, 1)


 BEGIN
  WHILE @Pos > 0
  BEGIN
   SET @NodeName = LTRIM(RTRIM(LEFT(@NodesList, @Pos - 1)))
   IF @NodesList <> ''
   BEGIN
    --the below statement will display the values one by one , you can manipulate the value here however you want
    SELECT @Line_Id, @NodeName
   END
   SET @NodesList = RIGHT(@NodesList, LEN(@NodesList) - @Pos)
   SET @Pos = CHARINDEX(',', @NodesList, 1)

  END

Basically @Pos is the position of the next comma, and it will keep looping through them one by one until the end.

Suriyaraj_Sudalaiappan,

This small function will split the string without using any additional I/O and
without the pipe_row overhead.  Plus, it's pretty simple and allows you to use ANY delimeter
(although it will default to commas).
 
create or replace function string_segment(INstring varchar2, Component binary_integer, delimeter char default ',') return varchar2 is
  Listing dbms_utility.lname_array;
  tablelen binary_integer;
begin
   DBMS_UTILITY.COMMA_TO_TABLE(replace(INstring,delimeter,','),tablelen,Listing);
   return (Listing(Component));
end;
/
 
Then select (using a loop subquery to get a row for every comma).  Limit the 
loop to the number of commas in the string.
 
select line_id, string_segment(node_name,loop_sequence) Node_Name
  from example, (select level loop_sequence from dual connect by level <= 100)
 where loop_sequence <= length(node_name) - length(replace(node_name,',','')) +1;
 
Gives:
   LINE_ID NODE_NAME
---------- ------------------------------
      1000 A
      1000 B
      1000 C
 
(if you supply a "col format node_name a30")
 
Good luck!
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

WHAT IS MEAN BY PIPELINED??

it means the function returns the data as it is being read.

as opposed to generating a (possibly) huge  table in its entirety and returning it all at once.
which is what DBMS_UTILITY.COMMA_TO_TABLE does.

pipelining does have processing overhead, but it uses less memory to store the data.  If you will always be using small amounts of data, then you probably don't need to worry about the savings.

So, if you compare the two methods posted by myself and DrSQL, you'll see they are "basically" the same idea.

Mine says....
Read your table, and for each node_name, split it into strings and return (sort of like a join) each piece.  If you added a where clause, the pipelining would stop returning data when the whereclause cut it off.

DrSQL's says,  read your table, for each node, split it into strings and then go lookup which element of the string you're looking for and return that piece.  If you added a where clause, the entire table would still be generated but would be filtered out.


From my testing, it appears the pipelining does less work.  With a simple 3-element string, both are extremely fast, but the pipeling does one less sort operation and uses less CPU (from v$mystat, CPU used by this session).  That's a tiny (very tiny) efficiency in favor of the pipelining,  I would expect it to be even more pronounced as the data volumes increase.  As with any performance comparisons,  test both yourself on your system.   Don't trust us.  :)  Try all suggestions and see which performs best for you.

hI sdstuber,
           i AM GETTING the answer like below

line_id          node_name
1000               A,B,C
1000               A,B,C
1000                A,B,C
                            How i can't get it what is the problem?

Or, combine the two approaches (if you go with this, it would be a split award).  Note that my solution was intended to be used as part of a select (it returns one record for every item in the list).  SD's is intended to be used in a procedure - it returns the list as a table.  Then you have the option of using the table function or just return the whole array to your procedure.

Good luck!

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
  Listing dbms_utility.lname_array;
  tablelen binary_integer;
  v_start number := 1;
BEGIN
   DBMS_UTILITY.COMMA_TO_TABLE(replace(p_string,p_delimiter,','),tablelen,Listing);
    for v_start in 1..tablelen
    LOOP
      PIPE ROW (Listing(v_start));
    END LOOP;
 
    RETURN;
END str2tbl; 
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

Below is my code, I am using whatever you says

SELECT
       lines_all.LINE_NUMBER, config_main.ITEM_VAL
FROM
      ASO_QUOTE_LINES_ALL lines_all,
      ASO_QUOTE_LINE_DETAILS line_details,
      CZ_CONFIG_ITEMS config_main,
        table(str2tbl(config_main.ITEM_VAL,','))
WHERE                        
     config_main.ps_node_name='pending_info_all'
     and config_main.CONFIG_HDR_ID = line_details.CONFIG_HEADER_ID
     and config_main.CONFIG_REV_NBR =  line_details.CONFIG_REVISION_NUM
     and lines_all.QUOTE_HEADER_ID = 43480--43186--43480
     and lines_all.QUOTE_LINE_ID = line_details.QUOTE_LINE_ID
     and lines_all.item_type_code='MDL'

Hi sdstuber,
       It's Really Great. Now it is working fine. What is mean by column_value item_val??  why you have added on that place. i am not inderstand this point and can you explain me about pipeline concept? This is the first time i heard about this. Please teach me.

the result returned from the TABLE function is called "column_value".  

So  by putting "column_value item_val"  I'm aliasing the column name to be called "item_val" like you wanted.

DrSQL,

" Note that my solution was intended to be used as part of a select (it returns one record for every item in the list).  SD's is intended to be used in a procedure "

I love our interactions.  I would have never thought of it that way.  In fact, I DID think of it exactly the opposite way!  If I were iterating through the data procedurally, I would probably use your method and call dbms_utility.   To put it in SQL, I would use pipelining, or if I knew I would always want the entire data set, then I would use the same external api, but instead of pipeline, I would build the collection my self and return it in its entirety.

SD,
   I agree.  I think my original method (not the hybrid I posted last) would be best used for selecting an individual item (like if I knew I wanted item #5 when a particular column was "DINOBURGERS" - a conditional lookup).  In that case it saves on recursive SQL even if it is re-splitting the string (in the case where there's more than one entry).  But, since it is returning a single value, not a table for the TABLE function, I saw it as being SQL driven.  Yours is more flexible in that you can just grab the whole table generically into a procedure or use the table function to get random or iterative access.  But I think the hybrid is probably the best.

Good luck!
P.S.  It's getting tempting to join in on any Q you post to, but I'm trying not to be rude.

I only chime in on questions you're already on either by accident with simul-posting, or if I see a different perspective or better option.  I certainly don't feel like I've "poached", nor do I feel you have either.

I have subscribed to a few of yours just to see where the threads went when I didn't have anything particularly useful to add.

Hi sdstuber,
         I got one problem. From the above query what i am getting is, 'pending_info_all' is the node_name in the table. It is stored in one column. This node_name is having the value like 'A,B,C,D' this comma separated value. My requirement is, this A and  B and C and D is the node_name of the some other table. Now i have to get the node_id for this node_name from other table. But it is getting only the node_id for 'pending_info_all' node_name. I want to get the node_id for A and  B and C and D. How can i get it? Can you do the needful?

Thanks

I'm not sure what you mean, but something like this maybe?

assuming your table with the node_names is called "some_other_table"  then try this...

SELECT 
       lines_all.LINE_NUMBER, column_value item_val
FROM 
      ASO_QUOTE_LINES_ALL lines_all, 
      ASO_QUOTE_LINE_DETAILS line_details, 
      CZ_CONFIG_ITEMS config_main,
        table(str2tbl(config_main.ITEM_VAL,',')) 
WHERE                         
     config_main.ps_node_name in (select column_value from some_other_table, table(some_other_table.node_name))
     and config_main.CONFIG_HDR_ID = line_details.CONFIG_HEADER_ID
     and config_main.CONFIG_REV_NBR =  line_details.CONFIG_REVISION_NUM
     and lines_all.QUOTE_HEADER_ID = 43480--43186--43480
     and lines_all.QUOTE_LINE_ID = line_details.QUOTE_LINE_ID
     and lines_all.item_type_code='MDL'

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

VERY GOOD SOLUTION