Re: Comma delimited values into rows

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 11 May 2006 14:10:21 -0400
Message-ID: <9YudnR7bL9eN4_7ZnZ2dnUVZ_vydnZ2d@comcast.com>

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1147367046.318985_at_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

"In sql only"

++ mcs Received on Thu May 11 2006 - 13:10:21 CDT