Re: convert delimited string to rows ?

Sunil wrote:
>
> I have a string
> 'rob:bob:tod:rod:mod'
> can I use any function to convert this into rows so that I can do something
> like
> SQL> select * from some_funct('rob:bob:tod:rod:mod')
> and get :-

• Oracle 8i CREATE OR REPLACE TYPE item AS OBJECT (p VARCHAR2(10)) / CREATE OR REPLACE TYPE arr AS TABLE OF item; / CREATE OR REPLACE FUNCTION sc2tab ( as_str VARCHAR2 ) RETURN arr IS la_items arr := arr(); BEGIN
  • your parser code here, it's just a stub la_items.EXTEND; la_items(1) := item(SUBSTR(as_str, 1, 3)); la_items.EXTEND; la_items(2) := item(SUBSTR(as_str, 5, 3)); la_items.EXTEND; la_items(3) := item(SUBSTR(as_str, 9, 3)); RETURN la_items; END sc2tab; /

SELECT a.p p
  FROM THE (

         SELECT CAST(sc2tab('abc:def:ghi') AS arr)
           FROM dual
       ) a

• Oracle 9i CREATE OR REPLACE FUNCTION sc2tab ( as_str VARCHAR2 ) RETURN arr PIPELINED IS BEGIN
  • your parser code has to be here, it's just a stub PIPE ROW(item(SUBSTR(as_str, 1, 3))); PIPE ROW(item(SUBSTR(as_str, 5, 3))); PIPE ROW(item(SUBSTR(as_str, 9, 3))); RETURN; END sc2tab; /

SELECT * FROM TABLE(sc2tab('abc:def:ghi')) /

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.