Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

divide the comma separated string into columns

SmileFeb 14 2012 — edited Apr 20 2012
Hi
I've the following string 
str := 'abcd,123,defoifcd,87765'
The above string has to be divided into 4 different columns

How can i achieve that

Thanks
This post has been answered by Frank Kulash on Feb 14 2012
Jump to Answer

Comments

Frank Kulash
Answer
Hi,

Use REGEXP_SUBSTR: 
SELECT  REGEXP_SUBSTR (str, '[^,]+', 1, 1)    AS part_1
,       REGEXP_SUBSTR (str, '[^,]+', 1, 2)    AS part_2
,       REGEXP_SUBSTR (str, '[^,]+', 1, 3)    AS part_3
,       REGEXP_SUBSTR (str, '[^,]+', 1, 4)    AS part_4
FROM    table_x
;
Can str contain emply items? For example, can you have a string like 'foo,,,bar', where you'd want to count part_2 and part_3 as NULL, and 'bar' is part_4? If so: 
SELECT  RTRIM (REGEXP_SUBSTR (str, '[^,]*,', 1, 1), ',')    AS part_1
,       RTRIM (REGEXP_SUBSTR (str, '[^,]*,', 1, 2), ',')    AS part_2
,       RTRIM (REGEXP_SUBSTR (str, '[^,]*,', 1, 3), ',')    AS part_3
,       LTRIM (REGEXP_SUBSTR (str, ',[^,]*', 1, 3), ',')    AS part_4
FROM    table_x
;
Edited by: Frank Kulash on Feb 14, 2012 8:46 AM
Marked as Answer by Smile · Sep 27 2020
3 people found this helpful
BluShadow
Splitting into rows or columns.... very commonly asked question on these forums...
SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'abcd,123,defoifcd,87765' as str from dual)
  2  --
  3  select level as n, regexp_substr(str,'[^,]+',1,level) as val
  4  from   t
  5* connect by regexp_substr(str,'[^,]+',1,level) is not null
SQL> /

 N VAL
-- -----------------------
 1 abcd
 2 123
 3 defoifcd
 4 87765

SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'abcd,123,defoifcd,87765' as str from dual)
  2  --
  3  select max(decode(level,1,regexp_substr(str,'[^,]+',1,level))) as val1
  4        ,max(decode(level,2,regexp_substr(str,'[^,]+',1,level))) as val2
  5        ,max(decode(level,3,regexp_substr(str,'[^,]+',1,level))) as val3
  6        ,max(decode(level,4,regexp_substr(str,'[^,]+',1,level))) as val4
  7        ,max(decode(level,5,regexp_substr(str,'[^,]+',1,level))) as val5
  8        ,max(decode(level,6,regexp_substr(str,'[^,]+',1,level))) as val6
  9        ,max(decode(level,7,regexp_substr(str,'[^,]+',1,level))) as val7
 10        ,max(decode(level,8,regexp_substr(str,'[^,]+',1,level))) as val8
 11        ,max(decode(level,9,regexp_substr(str,'[^,]+',1,level))) as val9
 12        ,max(decode(level,10,regexp_substr(str,'[^,]+',1,level))) as val10
 13  from   t
 14* connect by regexp_substr(str,'[^,]+',1,level) is not null
SQL> /

VAL1                    VAL2                    VAL3                    VAL4                    VAL5            VAL6                    VAL7                    VAL8                    VAL9             VAL10
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
abcd                    123                     defoifcd                87765

SQL>
1 person found this helpful
Solomon Yakobson 
with t as (
           select 'abcd,123,defoifcd,87765' as str from dual
          )
select  extractvalue(value(x), '/b') x
  from  t,
        table(
              xmlsequence(
                          xmltype('<a><b>' || replace(str, ',', '</b><b>') || '</b></a>' ).extract('/*/*')
                         )
             ) x
/

X
----------
abcd
123
defoifcd
87765

SQL>
SY.
Smile
Hi Frank,

Thank you for the query..

I didn't understand that
Why the last column is written as 
LTRIM (REGEXP_SUBSTR (str, ',[^,]*', 1, 3), ',')    AS part_4
Thank you
BluShadow
smile wrote:
Hi Frank,

Thank you for the query..

I didn't understand that
Why the last column is written as 
LTRIM (REGEXP_SUBSTR (str, ',[^,]*', 1, 3), ',')    AS part_4
Because there's no comma on the end of the string, so it has to be pattern matched differently with the regular expression.
BluShadow
Solomon Yakobson wrote: 
with t as (
select 'abcd,123,defoifcd,87765' as str from dual
)
select  extractvalue(value(x), '/b') x
from  t,
table(
xmlsequence(
xmltype('<a><b>' || replace(str, ',', '</b><b>') || '</b></a>' ).extract('/*/*')
)
) x
/

X
----------
abcd
123
defoifcd
87765

SQL>
SY.
Or with XMLtable...
SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'This is some sample text that needs splitting into words' as txt from dual)
  2  select x.*
  3  from t
  4      ,xmltable('x/y'
  5                passing xmltype('<x><y>'||replace(t.txt,' ','</y><y>')||'</y></x>')
  6                columns word varchar2(20) path '.'
  7*              ) x
SQL> /

WORD
--------------------
This
is
some
sample
text
that
needs
splitting
into
words

10 rows selected.
Frank Kulash
smile wrote:
Hi Frank,

Thank you for the query..

I didn't understand that
Why the last column is written as 
LTRIM (REGEXP_SUBSTR (str, ',[^,]*', 1, 3), ',')    AS part_4
In general , part_N can be defined either as "all the non-commas that come right before the Nth comma": 
,       RTRIM (REGEXP_SUBSTR (str, '[^,]*,', 1, N), ',')    AS part_N
(let's call this "Definition R", because the code uses RTRIM)
or part-N can be defined as "all the non-commas that come right after the (N-1)st comma". 
,       LTRIM (REGEXP_SUBSTR (str, ',[^,]*', 1, N-1), ',')    AS part_N
(let's call this "Definition L".)
In general , definitions R and L are equivalent.

There are 2 important exceptions to the above:
(1) The very first item on the list can not be defined as "all the non-commas that come right after the (N-1)st comma", because there is no 0th comma. The characters in the first item do not come after any comma.
(2) The very last item on the list can not be defined as "all the non-commas that come right before the Nth comma" beacuse there are only N-1 commas. The characters in the last item do not come before any comma.

In the query I posted earlier, part_2 and part_3 could be defined either way. I happened to use definition R for both of these columns, but I could have use L for either or both of them.
For part_1, that choice didn't exist. I cound not use definition L, so I used R.
Likewise, for part_4, again, there was no choice. I cound not use definition R, so I used L.
user9369213
I want to go further than just splitting a sentence into words. I am using 11g. Here is my scenario:

I want to split a sentence (space delimited) into word, and phrases. Phrases can have as much as three words.

For Example:

String : This is just a Test.
No of Words in String : 5

For above string, I want 5 + 4 + 3 = 12 different columns. Each column will have following data:
-------------------------------------------------------------------------------------------------------------------------------------


Col1* : This
Col2* : is
Col3* : just
Col4* : a
Col5* : Test
Col6* : This is
Col7* : is just
Col8* : just a
Col9* : a test
Col10*: This is just
Col11*: is just a
Col12*: just a test

I prefer to do this in plain SQL but if this is impossible in SQL, I can bargain PL/SQL as well.

Any suggestions/help?

Thank you all!
Thapa

Edited by: user9369213 on Apr 19, 2012 9:39 PM
BluShadow
user9369213 wrote:
I want to go further than just splitting a sentence into words. I am using 11g. Here is my scenario:

I want to split a sentence (space delimited) into word, and phrases. Phrases can have as much as three words.

For Example:

String : This is just a Test.
No of Words in String : 5

For above string, I want 5 + 4 + 3 = 12 different columns. Each column will have following data:
-------------------------------------------------------------------------------------------------------------------------------------


Col1* : This
Col2* : is
Col3* : just
Col4* : a
Col5* : Test
Col6* : This is
Col7* : is just
Col8* : just a
Col9* : a test
Col10*: This is just
Col11*: is just a
Col12*: just a test

I prefer to do this in plain SQL but if this is impossible in SQL, I can bargain PL/SQL as well.

Any suggestions/help?

Thank you all!
Thapa

Edited by: user9369213 on Apr 19, 2012 9:39 PM
Not easily possible as you are determining your number of columns based on the number of words in your data.
SQL Projection doesn't work like that.

Read this...

{thread:id=2309172}
BluShadow
If you wanted the data in rows that's easier...
SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'This is just a test' as txt from dual)
  2  --
  3  -- end of test data
  4  --
  5  select trim(sys_connect_by_path(word, ' ')) as word_phrase
  6  from (
  7        select x.*
  8        from t
  9            ,xmltable('x/y'
 10                      passing xmltype('<x><y>'||replace(t.txt,' ','</y><y>')||'</y></x>')
 11                      columns rn for ordinality
 12                             ,word varchar2(20) path '.'
 13                     ) x
 14       )
 15  where level <= 3
 16  connect by rn = prior rn + 1
 17* order by level, rn
SQL> /

WORD_PHRASE
--------------------------------------------------------------------------------------------
This
is
just
a
test
This is
is just
just a
a test
This is just
is just a
just a test

12 rows selected.
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 18 2012
Added on Feb 14 2012
#11g, #oracle, #query, #sql
10 comments
186,908 views