Thanks for the question, Terrie.

Asked: December 29, 2015 - 7:51 pm UTC

Last updated: May 11, 2022 - 5:40 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

My data is like the following:

ID HOSTS
--- -----
ID123 host1
host2
host3
ID124 host4
host5

The host column lists several values separated by return characters (not commas).

I want to output my result as follows:
ID123 host1
ID123 host2
ID123 host3
ID124 host4
ID124 host5

I have seen solutions where the source is a comma delimited list using LEVEL and CONNECT BY, but in this case it is line feeds / return characters.

Thanks for your help :)

and Connor said...

You can change the "l_delim" to whatever you want.

Hope this helps.

SQL> create or replace type myTable as table of varchar2(25)
  2  /

Type created.

SQL>
SQL> create or replace function str2tbl( p_str in varchar2 ) return myTable pipelined as
  2    l_delim varchar2(10) := chr(10)||chr(13);
  3    l_delim_length int := length(l_delim);
  4    l_str   long := p_str || l_delim;
  5    l_n        number;
  6  begin
  7          loop
  8              l_n := instr( l_str, l_delim );
  9              exit when (nvl(l_n,0) = 0);
 10             pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
 11             l_str := ltrim( substr( l_str, l_n + l_delim_length ) );
 12         end loop;
 13         return;
 14  end;
 15  /

Function created.

SQL>
SQL> drop table t purge;

Table dropped.

SQL> create table t ( id int, str varchar2(2000) );

Table created.

SQL>
SQL> insert into t values ( 1, '123'||chr(10)||chr(13)||'345'||chr(10)||chr(13)||'456');

1 row created.

SQL> insert into t values ( 2, '789'||chr(10)||chr(13)||'abc');

1 row created.

SQL>
SQL> select * from t;

  ID
----
STR
-------------------------------------------------------------------------------------------
   1
123
345
 456

   2
789
 abc


SQL> select t.id,  t2.column_value
  2   from t, TABLE(str2tbl(t.str)) t2
  3   /

  ID COLUMN_VALUE
---- -------------------------
   1 123
   1 345
   1 456
   2 789
   2 abc

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pure SQL Solution !!

Rajeshwaran, Jeyabal, December 30, 2015 - 6:56 am UTC

rajesh@ORA11G> variable x varchar2(3)
rajesh@ORA11G> exec :x := chr(10)||chr(13);

PL/SQL procedure successfully completed.

rajesh@ORA11G>
rajesh@ORA11G> select id,
  2     substr( :x||str||:x ,
  3             instr( :x||str||:x , :x ,1,column_value)+ length(:x) ,
  4             instr( :x||str||:x , :x ,1,column_value+1) -
  5             instr( :x||str||:x , :x ,1,column_value) -length(:x)) txt
  6  from t ,
  7     table( cast(multiset(select level
  8     from dual
  9     connect by level <= (length(str) -
 10             length(replace(str,:x)))/length(:x) +1)
 11             as sys.odcinumberlist) )
 12  /

        ID TXT
---------- --------------------
         1 123
         1 345
         1 456
         2 789
         2 abc

5 rows selected.

rajesh@ORA11G>

Using RegEx

rajesh@ORA11G> select id,
  2     regexp_substr(str,'[^'||:x||']+',1,column_value) txt
  3  from t ,
  4     table( cast(multiset(select level
  5     from dual
  6     connect by level <= (length(str) -
  7             length(replace(str,:x)))/length(:x) +1)
  8             as sys.odcinumberlist) )
  9  /

        ID TXT
---------- --------------------
         1 123
         1 345
         1 456
         2 789
         2 abc

5 rows selected.

rajesh@ORA11G>

12c - Lateral Inline views

Rajeshwaran, Jeyabal, December 30, 2015 - 7:05 am UTC

In case of 12c using Lateral inline views

rajesh@ORA12C> variable x varchar2(3)
rajesh@ORA12C> exec :x := chr(10)||chr(13);

PL/SQL procedure successfully completed.

rajesh@ORA12C> select id ,
  2     substr( :x||str||:x ,
  3             instr( :x||str||:x , :x ,1,x)+ length(:x) ,
  4             instr( :x||str||:x , :x ,1,x+1) -
  5             instr( :x||str||:x , :x ,1,x) -length(:x)) txt
  6  from t,
  7     lateral( select level x
  8             from dual
  9             connect by level <= (length(str) -
 10             length(replace(str,:x)))/length(:x) +1)
 11  /

        ID TXT
---------- --------------------
         1 123
         1 345
         1 456
         2 789
         2 abc

5 rows selected.

rajesh@ORA12C>

With Model Clause

Rajeshwaran, Jeyabal, December 30, 2015 - 7:24 am UTC

rajesh@ORA11G> variable x varchar2(3)
rajesh@ORA11G> exec :x := chr(10)||chr(13);

PL/SQL procedure successfully completed.

rajesh@ORA11G> select id,z
  2  from t
  3  model
  4     partition by (id)
  5     dimension by (0 y)
  6     measures( str, cast(null as varchar2(10)) z,
  7      cast(null as varchar2(10)) b1)
  8     rules iterate(10) until( b1[iteration_number] is null)
  9     ( z[iteration_number] = regexp_substr(str[0],'[^'||:x||']+',
 10                                  1,iteration_number+1) ,
 11       b1[iteration_number] = regexp_substr(str[0],'[^'||:x||']+',
 12                                  1,iteration_number+2)
 13          )
 14  /

        ID Z
---------- ----------
         1 123
         1 345
         1 456
         2 789
         2 abc

5 rows selected.

rajesh@ORA11G>

Convert delimited string to rows with SELECT

Terrie McClure, December 30, 2015 - 2:38 pm UTC

The tables are correct, but I'm trying to solve this with a SELECT statement instead of a function.
If the str values were separated by commas I think the following would work, but I need it to work with return characters like your example table. Thanks.

SELECT
id,
trim(regexp_substr(str, '[^,]+', 1, LEVEL)) as id
FROM t
CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0

Convert delimited string to rows with SELECT

Rajeshwaran, Jeyabal, December 31, 2015 - 6:27 am UTC

....
If the str values were separated by commas I think the following would work
....

no, it don't work.

rajesh@ORA10G> set feedback off
rajesh@ORA10G> drop table t purge;
rajesh@ORA10G> create table t(id int,str varchar2(10));
rajesh@ORA10G> insert into t values(1,'A,B,C');
rajesh@ORA10G> commit;
rajesh@ORA10G> set feedback on
rajesh@ORA10G> select * from T;

        ID STR
---------- ----------
         1 A,B,C

1 row selected.

rajesh@ORA10G> SELECT
  2  id,
  3  trim(regexp_substr(str, '[^,]+', 1, LEVEL)) as id
  4  FROM t
  5  CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 ;

        ID ID
---------- ----------
         1 A
         1 B
         1 C

3 rows selected.

with single row in Table 'T' this works, but when multiple rows exists, this breaks.

rajesh@ORA10G> insert into t values(2,'D,E');

1 row created.

rajesh@ORA10G> SELECT
  2  id,
  3  trim(regexp_substr(str, '[^,]+', 1, LEVEL)) as id
  4  FROM t
  5  CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 ;

        ID ID
---------- ----------
         1 A
         1 B
         1 C
         2 E
         1 C
         2 D
         1 B
         1 C
         2 E
         1 C

10 rows selected.

so the right solution would be this (Table un-nesting)

rajesh@ORA10G> select id,
  2     regexp_substr(str,'[^,]+',1,column_value) as x
  3  from t,
  4     TABLE(cast(multiset(select level
  5     from dual
  6     connect by level <= length(str) -
  7             length(replace(str,','))+1)
  8             as sys.odcinumberlist))
  9  /

        ID X
---------- ----------
         1 A
         1 B
         1 C
         2 D
         2 E

5 rows selected.

rajesh@ORA10G>

Try to stick with 'substr' and 'instr' approach rather than Regexp, "Regexp neat, but Huge CPU cost"

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:73830657104020#2325312300346613472

need query

Sipra, November 04, 2020 - 1:47 pm UTC

how we can write the query if both comma separated and & separated column values will be there
like1. A,B & C
2.A,B
3.C & D ,E
req o/p:
1 A
1 B
1 C
2 A
2 B
3 C
3 D
3 E

November 05, 2020 - 12:52 am UTC

instead of doing

select ...
from YOUR_TABLE

just do

select ..
from
  ( select col1, replace(col2,'&',',') col from YOUR_TABLE )

I Like Common Table Expressions

Brian Leach, November 11, 2020 - 12:07 am UTC

WITH
    crlf AS -- this is just to get crlf into a variable so I don't have to type chr(13) || chr(10) all the time
           ( SELECT CHR( 13 ) || CHR( 10 ) AS crlf FROM DUAL ),
    dset AS                                   -- setting up the raw data instead of creating a table
        (SELECT 'ID123' AS id, 'host1' || crlf || 'host2' || crlf || 'host3' AS hosts
           FROM DUAL
                CROSS JOIN crlf
         UNION ALL
         SELECT 'ID124' AS id, 'host4' || crlf || 'host5' AS hosts
           FROM DUAL
                CROSS JOIN crlf) -- you could do select * from dset at this point to verify we have the correct dataset
                                ,
    splitup ( id
            , HOST
            , hosts
            , crlf ) AS           -- here is where we split the single records into multiple records
        (SELECT id
              , SUBSTR( hosts
                      , 1
                      , INSTR( hosts, crlf ) - 1 )                                AS id -- id is the first element
              , SUBSTR( hosts, INSTR( hosts, crlf ) + LENGTH( crlf ) ) || crlf    AS hosts -- hosts are the remainder
              --  crlf appended to make logic easier
              --  length(crlf) used instead of '2' so that you can substitute any delimeter string for crlf
              , crlf          -- crlf is passed through to maintain ability to use it on each record
           FROM dset
                CROSS JOIN crlf             -- again, this is soley to bring crlf in and save typing
         UNION ALL -- next section identical to the first exception we don't append a CRLF and we select from splitup
         SELECT id
              , SUBSTR( hosts
                      , 1
                      , INSTR( hosts, crlf ) - 1 )    AS id
              , SUBSTR( hosts, INSTR( hosts, crlf ) + LENGTH( crlf ) )
              , crlf
           FROM splitup
          WHERE hosts IS NOT NULL)
  SELECT id, HOST
    FROM splitup
ORDER BY id, HOST

November 11, 2020 - 6:43 am UTC

I agree!

A reader, May 09, 2022 - 10:10 am UTC

How do i insert the result of select statement into a new table?When i do this: Inser into table 2(id,str) select(....your code above), it goes back to the original format of new line separated.

May 11, 2022 - 5:40 am UTC

Give us a full test case with all the output you're seeing

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Pure SQL Solution !!

12c - Lateral Inline views

With Model Clause

Convert delimited string to rows with SELECT

Convert delimited string to rows with SELECT

need query

I Like Common Table Expressions

More to Explore

PL/SQL demos

PL/SQL docs

split delimited column (by line feeds) to rows

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Pure SQL Solution !!

12c - Lateral Inline views

With Model Clause

Convert delimited string to rows with SELECT

Convert delimited string to rows with SELECT

need query

I Like Common Table Expressions

More to Explore

PL/SQL demos

PL/SQL docs