Skip to content
August 15, 2016 / doganay

STRING TO ROWS

SQL> create table t1(id number, str varchar2(11));

Table created.

SQL> insert into t1 values (1,'1,2,3,4,5');

1 row created.

SQL> insert into t1 values (2,'6,7,8,9,10');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID STR
---------- -----------
         1 1,2,3,4,5
         2 6,7,8,9,10

SQL> select id, subs from t1, xmltable(
  'if (contains($X,",")) then ora:tokenize($X,",") else $X'
  passing str as X
  columns subs varchar2(4000) path '.'
);

        ID SUBS
---------- --------
         1 1
         1 2
         1 3
         1 4
         1 5
         2 6
         2 7
         2 8
         2 9
         2 10


You can also use regexp_substr:

select regexp_substr('1,2,3,4','[^,]+', 1, level) COL1 from dual
connect by regexp_substr('1,2,3,4', '[^,]+', 1, level) is not null;

select id, regexp_substr (str, '[^,]+', 1, level) subs
from t1
connect by level <= length (regexp_replace (str, '[^,]+')) + 1
and id = prior id
and prior sys_guid() is not null;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: