오라클 split to COLUMN - olakeul split to COLUMN

Smile Member Posts: 762 Bronze Badge

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

Best 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

  
  

Answers

• 

  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

  
  

• BluShadow Member, Moderator Posts: 42,423 Red Diamond

  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>

• 

  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 Member Posts: 762 Bronze Badge

  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 Member, Moderator Posts: 42,423 Red Diamond

  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 Member, Moderator Posts: 42,423 Red Diamond

  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.

• 

  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.

• 

  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 Member, Moderator Posts: 42,423 Red Diamond

  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 Member, Moderator Posts: 42,423 Red Diamond

  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 discussion has been closed.