subrat_rout

 Profile Answers by subrat_rout 

• Jun 20th, 2008

 

I guess by using the help of INSTR and SUBSTR, we can break the one column(ename) data to two columns first and last column data.

astaputra

 Profile Answers by astaputra Questions by astaputra

• Jun 25th, 2008

 

select SUBSTR('Hunt, Michael',1,INSTR('Hunt, Michael',',')-1) LAST_NAME,SUBSTR('Hunt, Michael',INSTR('Hunt, Michael',',')+1) FIRST_NAME from dual

Here is the example how to use and accordingly change the query and insert into another table.

Thks...

sam2233

 Profile Answers by sam2233 

• Jul 3rd, 2008

 

 create table emp2 as(select Substr(ename,1,instr(ename,',')-1) as last,
   substr(ename,instr(ename,',')+1,length(ename)) as first  from emp1)

akhilbest

 Profile Answers by akhilbest 

• Oct 20th, 2008

 

insert into emp2(first_name,last_name) (select substr(ename,1,instr(ename,',')-1) ,trim(',' from substr(ename,instr(ename,','))) from emp1 )

Vishuraj

 Profile Answers by Vishuraj 

• Jan 9th, 2009

 

Insert into emp2select substring(ename,0,charindex(',',ename)),substring(ename,(charindex(',',ename)+ 1),40) from emp1This will insert into emp2 as First name and Lastname.

singh13

 Profile Answers by singh13 

• Feb 2nd, 2009

 

insert into emp2(first,last) (select substr(ename,1,instr(ename,',')-1),substr(ename,instr(ename,',')+1) from emp1)

jaiswalabhi1

 Profile Answers by jaiswalabhi1 

• Jul 16th, 2009

 

insert into emp2( select substr(ename,1,instr(ename,',',1)-1) from emp,select substr(ename,instr(ename,',',1)+1) from emp))

gudluck

 Profile Answers by gudluck 

• Jul 16th, 2009

 

SELECT CONCAT(substring_index(Ename,",",-1),substring_index(Ename,",",1)) FROM Emp1;

(Tested in mySQL)

Nehasinha

 Profile Answers by Nehasinha Questions by Nehasinha

• Jul 17th, 2009

 

CREATE TABLE emp3 AS SELECT * FROM emp2;

DROP TABLE emp2;

RENAME emp1 TO emp2;

RENAME emp3 TO emp2;

vinu1

 Profile Answers by vinu1 

• Dec 8th, 2009

 

CREATE TABLE emp2
AS
SELECT substr(ename,instr(ename,',')+1) First,substr(ename,1,instr(ename,',')-1)  Last FROM emp1;

pollysingh544

 Profile Answers by pollysingh544 

• Feb 20th, 2010

 

Last value in emp1 table "Nelson,Ryan" having the length of 11 different from other values having length of 12.

Not poosible in this case, if it also could be the same length field then it can be resolved using substr() and Trim() functions.

sarasmuthu

 Profile Answers by sarasmuthu 

• Feb 21st, 2010

 

INSERT INTO emp2 SELECT substr(ename,1,instr(ename,',') -1) as LN , substr(ename,instr(ename,',') +1, length(ename)) as FN FROM emp1;

SujitKr

 Profile Answers by SujitKr 

• Feb 22nd, 2010

 

CREATE TABLE emp2 as SELECT  (SUBSTR(ename,instr(ename,',')+2) ||' ' || SUBSTR(ename,0,instr(ename,',')-1)) ename FROM test

Sujit

aryasen.kp

 Profile Answers by aryasen.kp 

• Mar 3rd, 2010

 

CREATE TABLE emp2 as SELECT Initcap(trim(substr(Ename,instr(Ename,',')+1,length(Ename)-instr(Ename,',')))) ||' '||Initcap(trim(substr(Ename,1,instr(Ename,',')-1))) firstlast
FROM emp1

debjit20

 Profile Answers by debjit20 

• Apr 9th, 2010

 

Pls try the following steps:

Create table emp1
(ename varchar2(30))
create table emp2
(first_name varchar2(30), last_name varchar2(30))
insert into emp1 values('Hunt, Michael')
insert into emp1 values('Graham, Smith')
insert into emp1 values('Nelson, Ryan')
select * from emp1
insert into emp2
select trim(substr(ename, instr(ename, ',')+1, length(ename))), trim(substr(ename, 1, instr(ename, ',')-1))
from emp1
select * from emp2

The output will be:

FIRST_NAME   LAST_NAME
Michael              Hunt
Smith                 Graham
Ryan                   Nelson

katgeektalk

 Profile Answers by katgeektalk 

• Aug 22nd, 2010

 

SELECT substr(ename instr(ename, ',')+2) First ,substr(ename, 1, instr(ename, ',')-1) Last FROM emp1;
has to be +2 because of the space after comma

yagneswarasankar

 Profile Answers by yagneswarasankar 

• Nov 16th, 2010

 

SQL> select * from name;

NAME
--------------------
Girija,Sankar


  1* create table split_name as(select substr(name,1,(instr(name,',') -1)) "First",substr(name,(inst
SQL> /

Table created.

SQL> select * from split_name;

First                Last
-------------------- --------------------
Girija               Sankar