SQL - Data Conversion

GeekInterview.com

I am new, Sign me up!

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 166 of 171

Print

Data Conversion

Have 2 table Emp1 (Column Name: Ename), 2nd table Emp2 (Column Name: First and Column Name: Last

Table Name :Emp1
EName
Hunt, Michael
Graham, Smith
Nelson, Ryan

Table Name: Emp2
First Last
Michael Hunt
Smith Graham
Ryan Nelson

Need to write a SQL query to convert the data from Emp1 to Emp2 in this way.
Can any body help me.

Thanks

Total Answers and Comments: 9

Last Update: July 17, 2009 Asked by: ssq1999

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

June 20, 2008 11:11:29

#1

Member Since: October 2005 Contribution: 1

RE: Data Conversion

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.

Is this answer useful? Yes | No

June 25, 2008 22:31:29

#2

Member Since: June 2008 Contribution: 7

RE: Data Conversion

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

Is this answer useful? Yes | No

July 03, 2008 10:19:15

#3

Member Since: July 2008 Contribution: 1

RE: Data Conversion

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

Is this answer useful? Yes | No

October 20, 2008 10:22:02

#4

Member Since: October 2008 Contribution: 1

RE: Data Conversion

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

Is this answer useful? Yes | No

January 09, 2009 13:52:02

#5

Member Since: January 2009 Contribution: 3

RE: Data Conversion

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.

Is this answer useful? Yes | No

February 02, 2009 00:30:52

#6

Member Since: February 2009 Contribution: 2

RE: Data Conversion

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

Is this answer useful? Yes | No

July 16, 2009 02:06:36

#7

Member Since: July 2009 Contribution: 1

RE: Data Conversion

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

Is this answer useful? Yes | No

July 16, 2009 09:56:03

#8

Member Since: July 2009 Contribution: 5

RE: Data Conversion

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

(Tested in mySQL)

Is this answer useful? Yes | No

July 17, 2009 01:53:40

#9

Member Since: July 2009 Contribution: 3

RE: Data Conversion

CREATE TABLE emp3 AS SELECT * FROM emp2;

DROP TABLE emp2;

RENAME emp1 TO emp2;

RENAME emp3 TO emp2;

Is this answer useful? Yes | No

0 1

Overall Rating: -1

Sponsored Links

Sponsored Links

About Us - Privacy Policy - Terms and Conditions - Contact - Ask Question - Propose Category - Site Updates

Copyright © 2005 - 2009 GeekInterview.com. All Rights Reserved