| help for sql [message #21112] |
Fri, 12 July 2002 20:29  |
Xena
Messages: 4
Registered: June 2002
|
Junior Member |
|
|
I have one table say emp
it has two column (SSNO Number primery key,
Address varchar2)
and address column data looks like
245 Michelle cir , Houston ,TX
Now, i want new table emp1 without address colume and
address column is break in to three new columns
say Street , City and street.
Please tell me the SQL how to do it ....??
Thanx
Warrior Xena
|
|
|
|
| Re: help for sql [message #21113 is a reply to message #21112] |
Fri, 12 July 2002 21:54  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You might need to play with the offsets a bit, but this should get you started:
create table emp1
(ssno number primary key,
street varchar2(30),
city varchar2(30),
state varchar2(2));
insert into emp1
select ssno,
trim(substr(address, 1, instr(address, ',') - 1)),
trim(substr(address, instr(address, ',') + 1, instr(address, ',', 1, 2) - instr(address, ',') - 1)),
trim(substr(address, instr(address, ',', 1, 2) + 1, 2))
from emp; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
