Split one column into two columns using pl/sql

[keerti_rose]

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

[admin]

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>

[keerti_rose]

Hi,

Thank you...But...

If i have ename like this

1234rama
13razaq
678raju
090034yesu

This is my requirement..How we will split this...

[ahmadbilal]

try isnumber and length function in substr.

for isnumber see http://www.arikaplan.com/oracle/ari021000.html

[keerti_rose]

Hi Mate,

Thanks a lot