divide the comma separated string into columns

1. How to split comma separated string and pass to IN clause of select statement

By Arunkumar Ramamoorthy on May 04, 2010

In some cases, we get a comma separated string as output (say from another select statement) that we would need to pass to the IN clause of a select statement. 

This article explains how to achieve that using regexp_substr (DB >=10g). 

For example, assume a select statement returns the following 

'SMITH,ALLEN,WARD,JONES'

Now, we would need to pass this to another select statement as IN clause and get the output. 

SQL> select * from emp where ename in ('SMITH,ALLEN,WARD,JONES');
no rows selected

Well, this is not our expected output. We expect the query to return 4 rows. 

This can be achieved by splitting the comma separated string to individual strings and pass it to the IN clause. 

Oracle provides regexp_substr function, which comes handy for this scenario. 

First, we will form a query, that splits this comma separated string and gives the individual strings as rows. 

SQL> select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
  2  connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;

REGEXP_SUBSTR('SMITH,A ---------------------- SMITH ALLEN WARD JONES

The above query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter. 

We can pass this query to our select statement to get the desired output. 

SQL> select * from emp where ename in (
  2  select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
  3  connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null );

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20

Now, the query returns what we expected. 

 

2. https://forums.oracle.com/thread/2348338

  •  
  • 1. Re: divide the comma separated string into columns
    FrankKulashGuru

    FrankKulash Feb 14, 2012 1:51 PM (in response to Smile)

    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

  • Helpful Answer2. Re: divide the comma separated string into columns
    BluShadowGuru Moderator

    BluShadow Feb 14, 2012 1:55 PM (in response to Smile)

    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>
  • 3. Re: divide the comma separated string into columns
    SolomonYakobsonGuru

    SolomonYakobson Feb 14, 2012 2:22 PM (in response to Smile)

    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.

  • 4. Re: divide the comma separated string into columns
    SmileNewbie

    Smile Feb 14, 2012 2:29 PM (in response to FrankKulash)

    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

  • 5. Re: divide the comma separated string into columns
    BluShadowGuru Moderator

    BluShadow Feb 14, 2012 2:47 PM (in response to Smile)

    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.

  • 6. Re: divide the comma separated string into columns
    BluShadowGuru Moderator

    BluShadow Feb 14, 2012 2:48 PM (in response to SolomonYakobson)

    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.
  • 7. Re: divide the comma separated string into columns
    FrankKulashGuru

    FrankKulash Feb 14, 2012 7:26 PM (in response to Smile)

    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.

  • 8. Re: divide the comma separated string into columns
    931985Newbie

    931985 Apr 20, 2012 4:48 AM (in response to BluShadow)

    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

  • 9. Re: divide the comma separated string into columns
    BluShadowGuru Moderator

    BluShadow Apr 20, 2012 7:52 AM (in response to 931985)

    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}

  • 10. Re: divide the comma separated string into columns
    BluShadowGuru Moderator

    BluShadow Apr 20, 2012 7:57 AM (in response to 931985)

    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.
This entry was posted in Uncategorized. Bookmark the permalink.

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