Re: Comma delimited values into rows

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 11 May 2006 10:04:05 -0700
Message-ID: <1147367046.318985@bubbleator.drizzle.com>

bbulsara23_at_hotmail.com wrote:

> fitzjarrell_at_cox.net wrote:

>> johnv wrote:
>>> I have this string in a field called 'ID':
>>>
>>> 1000,1001,1002,...n
>>>
>>> In sql only, how can i write a view that treats the comma as a new row
>>> so that my result set reads as:
>>>
>>> ID
>>> -------
>>> 1000
>>> 1001
>>> 1002
>> SQL> select id
>> 2 from listest;
>>
>> ID
>> --------------------------------------------------------------------------------
>> 1000,1001,1002,1003,1004
>>
>> SQL> select translate(id, ',', chr(10)) from listest;
>>
>> TRANSLATE(ID,',',CHR(10))
>> --------------------------------------------------------------------------------
>> 1000
>> 1001
>> 1002
>> 1003
>> 1004
>>
>>
>> SQL>
>>
>>
>> David Fitzjarrell

> 
> 
> Which, David, is NOT what he wants! His question was
> 

>> In sql only, how can i write a view that treats the
>> comma as a new row so that my result set reads as:

> 
> Note the **treats the comma as a new row**. Your solution doesn't meet
> this requirement. All your solution does is substitute a comma for a
> \n, the data is still one row and he still has the same problem!
> Regards
> Barry

Then try this:

CREATE OR REPLACE TYPE str_array AS TABLE OF VARCHAR2(10); /

CREATE OR REPLACE FUNCTION tf (stringin VARCHAR2) RETURN str_array PIPELINED IS
  i PLS_INTEGER;
  str VARCHAR2(100);
  tab sys.dbms_utility.uncl_array;
BEGIN
   str := '"' || REPLACE(stringin, ',', '","') || '"';    sys.dbms_utility.comma_to_table(str, i, tab);    FOR j IN 1 .. 5 LOOP
     PIPE ROW(TRANSLATE(tab(j),'A"','A'));    END LOOP;
   RETURN;
END;
/

SELECT *
FROM TABLE(CAST(tf('1001,1002,1003,1004,1005') AS str_array));

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu May 11 2006 - 12:04:05 CDT