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.
Printable View
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.