Break one column to multiple columns

[sree_sri]

Hi Guys,

In one column i have a values like this.
Full Name
---------------
"Keith Warn"
"Hary Peter"
"Mag B Boston"

I want to break that column as 2 columns. like this..
First_column second column Third_column
----------------- ----------------- -----------------
Keith Warn
Hary Peter
Mag b Boston

Any ideas? Thanks.

Sree.

[anandkl]

U can do only for reporting purpose using query like
select column1 col1,column1 col2 from table_name;

U cannot break or split a column.Since there are attributes of an entity you need to create them explicitly...

regards
anandkl

[Edited by anandkl on 07-12-2002 at 06:32 PM]

[sree_sri]

Thanks for fast respone.

For example if we take INITCAP command it will make first letter capitale to all words.

For example.

SELECT INITCAP('the soap') "Capitals" FROM DUAL;

Capitals
---------
The Soap

"INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. "

So oracle is recognizing the difference between words in a column by delimiting the spaces.

But i don't know why oracle is not provided any function for this.

Any body have other ideas on this. Any suggestion will help me. Thanks.

Sree.

[stecal]

But, what you can do is select a substring out of the column, which is hokey because of where the break in the name occurs, OR use PL/SQL to select each row into a cursor, then compare each character in the name string to an array - once you hit a space, take what's been selected out into a concatenated name string and do an insert into a new, properly formed table. From there, take the remaining characters in the array, minus the space, concatenate those together, and do the second insert. Sounds like a pain in the butt? Yes. Should have created the table correctly in the first place, but all may not be lost. How was the data put in the table? One line at a time by hand? Or with a script? If with a script, modify it accordingly. If by hand, live and learn.

[sree_sri]

Hi Stecal,

Ha...Ha.. Good Suggestion. I agree with you. Should have table created correctly.

This is a old data which is entered by users long time back.
Now we want to change that field from one to 3 fields.

But thanks for spending your time on this.

Hi Guys, Is there another way. Any suggestions welcome.

Sree.

[stecal]

Okay, another way: select * from bad_table into a flat file - save it out to an external file that is tab delimited. Edit the file as necessary, then use SQL Loader to read in the flat file. How's that work for you?

[sree_sri]

30 columns and 6,50,000 records. Any other idea's.

Sree.

[jmodic]

As I can understand, you want to break the contents of your COL_X into three different columns COL_1 to COL_3 undeer the following rules:

COL_1: everything from first character up to the first blank character
COL_2: everything from the first blank to the second blank
COL_3: everything following the second blank

So here is somewhat messy code, but it should work:

SELECT SUBSTR(col_x, 1, DECODE(INSTR(col_x, ' ', 1, 1), 
                                   0, LENGTH(col_x),
                                   INSTR(col_x, ' ', 1, 1)-1)
                                  ) AS col_1,
       SUBSTR(col_x, DECODE(INSTR(col_x, ' ', 1, 1), 
                                   0, LENGTH(col_x)+1,
                                   INSTR(col_x, ' ', 1, 1)+1),
                         DECODE(INSTR(col_x, ' ', 1, 2), 
                                   0, LENGTH(col_x),
                                   INSTR(col_x,' ', 1, 2)-1)
                                  ) AS col_2,
       SUBSTR(col_x, DECODE(INSTR(col_x, ' ', 1, 2), 
                                   0, LENGTH(col_x)+1,
                                   INSTR(col_x, ' ', 1, 2)+1)) AS col_3
FROM my_table;

[sree_sri]

Hi Jmodic,

Really you guys are great. Your solution is great but still it's not working completly.

In the table i have values like this

harry peter
mag b boston
Keith warn

your query output is

COL_1 COL_2 COL_3
-------------------- -------------------- --------------------
harry peter
mag b bos boston
Keith warn

That bos is repeting. And if i have another value
like
"ben j sam jr" then i want to break into 4 columns.

Sorry, I know that i am giving trouble to you guys. But this is a big problem to me. So please don't take me bad.

Thanks Jmodic and stecal.

[stecal]

I'm impressed with the effort jmodic put into this. However, since you claim it is not working for you, I suggest one of two courses of action:

1) Suck up to jmodic and ask him to further refine what he did.

2) Do the string array routine. That is guaranteed to work. Everytime you hit a space, an insert is made, and the rest of the array is processed. End of the array --> go to the next string in your cursor. Takes a lot more processing, but hey, what do you care? That's what computers are for.