SQL string manipulation to return multiple rows

Question

I'm an experienced programmer, but relatively new to SQL. We're using Oracle 10 and 11. I have a system in place using SQL that combines actual rows with virtual rows (e.g. "SELECT 1 from DUAL") doing unions and intersects as needed, which all seems to work.

My problem is that I need to combine this system which is expecting rows of data, with new data that will have the data in (let's say for simplification) comma delimited strings.

So I think what I need is a way to convert a string like: "5,6,7,8" into 4 rows with one column each, with "5" in the first row, "6" in the second, etc. In other languages, I'd do a "Split" with comma as the delimiter. Of course, the data won't always have 4 entries.

There's a second question, but I'll ask it separately. But I suspect it will simplify things, if possible, if the solution to the above could be used as a table in another SQL statement (i.e., to work with my existing system). Thanks for any help.

Answer 1

You should really extract that comma separated string into an outside program in any language, split it up, bulk-load it into a temp table, and use that temp table in your queries.

If you absolutely HAVE to do it in SQL, this article shows you how to do so:

http://www.oracle.com/technology/oramag/code/tips2007/070907.html

Answer 2

It looks ugly, but it works:

select r
      ,substr(','||csv||',',
              instr(','||csv||',',',',1,r)+1,
              instr(','||csv||',',',',1,r+1)-instr(','||csv||',',',',1,r)-1) v
from   (select '5,6,7a,8b,,bob' csv from dual)
      ,(select rownum r from dual connect by level <= 4000)
where  instr(csv||',',',',1,r) > 0;

R   V
=   =
1   5
2   6
3   7a
4   8b
5
6   bob

Answer 3

For SQL use you can do the following...

CREATE OR REPLACE TYPE tab_varchar2 AS TABLE OF VARCHAR2( 4000 );
/

CREATE OR REPLACE FUNCTION string_to_rows
   ( pv_string   IN   VARCHAR2
   , pv_delimiter   IN   VARCHAR2   DEFAULT   '_'
   )
   RETURN tab_varchar2
   PIPELINED
AS
   lv_string   VARCHAR2( 32767 )   DEFAULT   pv_string || pv_delimiter;
   lv_num   PLS_INTEGER;
BEGIN
   LOOP
      lv_num := INSTR( lv_string, pv_delimiter );
      EXIT WHEN ( NVL( lv_num, 0 ) = 0 );

      PIPE ROW( LTRIM( RTRIM( SUBSTR( lv_string, 1, lv_num - 1 ) ) ) );

      lv_string := LTRIM( SUBSTR( lv_string, lv_num + 1 ) );

    END LOOP;

    RETURN;
END;
/

Then you can run something like this...

SELECT c.owner, c.table_name, c.column_name, c.data_type
     , t.column_value   column_token
  FROM dba_tab_columns c
     , TABLE( string_to_rows( c.column_name ) ) t
 WHERE c.owner = 'SYS'
   AND c.table_name = 'DBA_INDEXES'
   AND c.column_name = 'AVG_LEAF_BLOCKS_PER_KEY'

Which would return this...

Row# OWNER TABLE_NAME  COLUMN_NAME             DATA_TYPE COLUMN_TOKEN
---- ----- ----------- ----------------------- --------- ------------
1    SYS   DBA_INDEXES AVG_LEAF_BLOCKS_PER_KEY NUMBER    AVG
2    SYS   DBA_INDEXES AVG_LEAF_BLOCKS_PER_KEY NUMBER    LEAF
3    SYS   DBA_INDEXES AVG_LEAF_BLOCKS_PER_KEY NUMBER    BLOCKS
4    SYS   DBA_INDEXES AVG_LEAF_BLOCKS_PER_KEY NUMBER    PER
5    SYS   DBA_INDEXES AVG_LEAF_BLOCKS_PER_KEY NUMBER    KEY