OraFAQ Forum: Client Tools » Splitting a column into multiple columns

Home » Other » Client Tools » Splitting a column into multiple columns (Oracle 11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Splitting a column into multiple columns [message #562361]

Tue, 31 July 2012 13:57

infinitenumbers
Messages: 33
Registered: November 2010

Member

Hi Guys,

I need to split a column into multiple columns. The data in my column is separated by a Comma (,). But the data is dynamic and I could have any number of data separated by (,).

Quote:

FOR Ex:
If COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION, ITEM_DATA_TYPE

RESULT: should be 4 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION and ITEM_DATA_TYPE

Is COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION

RESULT:
should be 3 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION

Your help is much appreciated.

Thanks in advance,
shil

Report message to a moderator

Re: Splitting a column into multiple columns [message #562363 is a reply to message #562361]

Tue, 31 July 2012 14:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

having comma separated string in single column violates data normalization rules.

Report message to a moderator

Re: Splitting a column into multiple columns [message #562364 is a reply to message #562361]

Tue, 31 July 2012 14:15

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is a FAQ, please search before posting.

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Here's an example:

SQL> var mylist varchar2(100)
SQL> exec :mylist := '5,11,13,22,23,31,44,45'

PL/SQL procedure successfully completed.

SQL> select substr(:mylist,
  2                instr(','||:mylist||',', ',', 1, rn),
  3                instr(','||:mylist||',', ',', 1, rn+1)
  4                - instr(','||:mylist||',', ',', 1, rn) - 1) value
  5  from ( select rownum rn from dual 
  6         connect by level 
  7                      <= length(:mylist)-length(replace(:mylist,',',''))+1
  8       )
  9  /
VALUE
-----------------------------------------------------------
5
11
13
22
23
31
44
45

This use a row generator.

Regards
Michel

Report message to a moderator

Re: Splitting a column into multiple columns [message #565261 is a reply to message #562364]

Sun, 02 September 2012 07:23

rvijayaragavan86
Messages: 6
Registered: September 2012
Location: Bangalore

Junior Member

You could try using dbms_utility.comma_to_table procedure

Report message to a moderator

Previous Topic:	Query stored Unicode URDU Data through SQL*PLUS
Next Topic:	Comment on Column with & symbol

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 05 23:49:26 CST 2025