-
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.
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.
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.
sree
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|