In one column i have a values like this.
"Mag B Boston"
I want to break that column as 2 columns. like this..
First_column second column Third_column
----------------- ----------------- -----------------
Mag b Boston
Any ideas? Thanks.
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...
[Edited by anandkl on 07-12-2002 at 06:32 PM]
Thanks for fast respone.
For example if we take INITCAP command it will make first letter capitale to all words.
SELECT INITCAP('the soap') "Capitals" FROM DUAL;
"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.
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.
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.
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?
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),
INSTR(col_x, ' ', 1, 1)-1)
) AS col_1,
SUBSTR(col_x, DECODE(INSTR(col_x, ' ', 1, 1),
INSTR(col_x, ' ', 1, 1)+1),
DECODE(INSTR(col_x, ' ', 1, 2),
INSTR(col_x,' ', 1, 2)-1)
) AS col_2,
SUBSTR(col_x, DECODE(INSTR(col_x, ' ', 1, 2),
INSTR(col_x, ' ', 1, 2)+1)) AS col_3
30 columns and 6,50,000 records. Any other idea's.
Really you guys are great. Your solution is great but still it's not working completly.
In the table i have values like this
mag b boston
your query output is
COL_1 COL_2 COL_3
-------------------- -------------------- --------------------
mag b bos boston
That bos is repeting. And if i have another value
"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.
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.