Printable View
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.
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]
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.
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.
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.
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:
Code: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;
30 columns and 6,50,000 records. Any other idea's.
Sree.
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.
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.
Hi Stecal,
Thanks for sucking idea, Surely Jmodic gave me very good
start. I think i can solve this problem with out sucking.
Anyway once again thanks for valuable time and valuable
suggestion.
Sree.
Hi Jmodic&Stecal,
I modified that code like this and it's working for 3 columns.
SELECT
substr(dname,1,INSTR(dname, ' ', 1, 1))
AS column_1,
substr(dname,INSTR(dname, ' ', 1, 1)+1,decode(instr(dname,' ',instr(dname, ' ',1,1)+1,1),0,50,instr(dname,' ',instr(dname, ' ',1,1)+1,1))-
INSTR(dname, ' ', 1, 1))
AS column_2,
SUBSTR(dname, DECODE(INSTR(dname, ' ', 1, 2),
0, LENGTH(dname)+1,
INSTR(dname, ' ', 1, 2)+1)) AS column_3
from dept
;
Now result looking like this.
<------------------- ----------------- -----------
harry peter
mag b boston
Keith warn
Thanks Jmodic, Now i am working for 4 columns.
Sree.
Hi Jmodic,
I successfully divided one column into 4 columns. This is the code.
SELECT
SUBSTR(DNAME,1,INSTR(dname, ' ', 1, 1)) AS COLUMN_1,
SUBSTR(DNAME,INSTR(dname, ' ', 1, 1),
DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1), 1),0,70,
INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1), 1))-INSTR(dname, ' ', 1, 1)) AS COLUMN_2,
SUBSTR(DNAME,DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1), 1),0,70,
INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1), 1)),
DECODE(INSTR(dname, ' ',DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1)),0,0,INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1))+1),1),0,70,INSTR(dname, ' ',DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1)),0,0,INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1))+1),1)
)-INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1), 1)
) AS COLUMN_3,
SUBSTR(DNAME,
DECODE(INSTR(dname, ' ',DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1)),0,0,INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1))+1),1),0,70,INSTR(dname, ' ',DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, '
',1,1)+1,1)),0,0,INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1))+1),1))) AS COLUMN_4
from dept;
My data in table is
Full Name
------------
Equips0 1
CHAS AGOT
JUAN ALBOR
MARIANN ALEMAN
WING KEEN ALVIN SEE
PAUL ANDERSON
SCOTT L ANDERTON
BOUNPONE APHAY
DENNIS ATTAR
When i ran that query data is displaying like this.
COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4
------------- -------- ---------- --------------
Equips0 1
CHAS AGOT
JUAN ALBOR
MARIANN ALEMAN
WING KEEN ALVIN SEE
PAUL ANDERSON
SCOTT L ANDERTON
BOUNPONE APHAY
DENNIS ATTAR
I think this script will solve my problem, Thanks for giving
me push on this. Really i appriciate your help. Bye..
Sree.