table emp
This ia table and it's records
--------------------
|ename | date |
---------------------
|12rama |12-01-08|
|34sapa |11-03-08|
|76oral |01-04-08|
---------------------
Now i want to create a separate table "employee"with three column(no,name,date)
-----------------------
|no | name | date |
------------------------
|12 | rama |12-01-08|
|34 | sapa |11-03-08|
|76 | oral |01-04-08|
------------------------
Can you please send the pl/sql code how to do this
We can do like this like if that column a-z or 0-9..But i am not able to implement.
Please help me in this...
Split one column into two columns using pl/sql
-
- Posts: 40
- Joined: Sat Mar 01, 2008 3:39 pm
- Location: India
Check the following example,
SQL> select * from dept;
DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> create table t as select deptno||dname deptno_name, loc from dept;
Table created.
SQL> select * from t;
DEPTNO_NAME LOC
------------------------------------------------------ -------------
10ACCOUNTING NEW YORK
20RESEARCH DALLAS
30SALES CHICAGO
40OPERATIONS BOSTON
SQL> create table t2 as select substr(deptno_name, 1,2) deptno, substr(deptno_name, 3,20) dname, loc from t;
Table created.
SQL> select * from t2;
DE DNAME LOC
-- -------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SQL> select * from dept;
DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> create table t as select deptno||dname deptno_name, loc from dept;
Table created.
SQL> select * from t;
DEPTNO_NAME LOC
------------------------------------------------------ -------------
10ACCOUNTING NEW YORK
20RESEARCH DALLAS
30SALES CHICAGO
40OPERATIONS BOSTON
SQL> create table t2 as select substr(deptno_name, 1,2) deptno, substr(deptno_name, 3,20) dname, loc from t;
Table created.
SQL> select * from t2;
DE DNAME LOC
-- -------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
-
- Posts: 40
- Joined: Sat Mar 01, 2008 3:39 pm
- Location: India
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
try isnumber and length function in substr.
for isnumber see http://www.arikaplan.com/oracle/ari021000.html
for isnumber see http://www.arikaplan.com/oracle/ari021000.html
Who is online
Users browsing this forum: No registered users and 0 guests