Question

there is a table EMPLOYEE contains 3 columns (name, email, job) in sql:

+--------------------------+--------------------------------------------+-----------+
| name | email | job     |
+--------------------------+--------------------------------------------+-----------+
| Jack Smith   | [Jack11@abcd.com]    | Teacher    |
| Rock Jol    | [Rock.jol@jfhro.com]    | cashier |
| Andrew stanley | [ANDREW80@hfwjs.com] | student    |
+--------------------------+--------------------------------------------+-----------+

create second table EMPLOYEE2 which has three columns as follows (in sql):

Jack Smith   Jack11@abcd.com Teacher

Hint: can use update EMPLOYEE2 set email = ....

Answer 1

Solution:

Creating employee table and inserting rows:

create table employee(name char(100),email varchar(100),job char(20));

insert into employee values ("Jack Smith","[Jack11@abcd.com]","Teacher"),("Rock Jol","[Rock.jol@fhro.com]","cashier"),("Andrew Stanley","[ANDREW80@hfwjs.com]","student");

Creating employee2 table:

create table employee2(name char(100),email varchar(100),job char(20));

Here insert rows into employee2 table from employee table by removing bracets [] in email column.By using substr() function we can achieve that task.

Inserting rows to employee2 table:

insert into employee2 (name,email,job) select name,substr(email,2,length(email)-2),job from employee;

Here substr() function takes the email string from 2nd index and then it will extract length(email)-2 characters it will give last before position of the string.

Code and Output Screenshots:

mysql> create table employee(name char(100), email varchar(100),job char(20)); Query OK, O rows affected (0.54 sec) mysql> insert into employee values ("Jack Smith", "[Jack11@abcd.com]", "Teacher"), ("Rock Jol","[Rock.jol@fhro.com)", "cashier"),("Andrew Stanley", "[ANDREW88@hfwjs.com]","s! tudent"); Query OK, 3 rows affected (0.15 sec) Records: 3 Duplicates: © Warnings: 0 mysql> create table employee2(name char(100), email varchar(100), job char(20)); Query OK, o rows affected (0.62 sec) mysql> insert into employee2 (name, email, job) select name, substr(email,2, length(email)-2), job from employee; Query OK, 3 rows affected (0.19 sec) Records: 3 Duplicates: © Warnings: @ mysql> select * from employee; ------- ---------+ | name email job | --------- ------- - ---------------------+---------+ Jack Smith | [Jack11@abcd.com] Teacher Rock Jol (Rock.jol@fhro.com] cashier Andrew Stanley | [ANDREW80@hfwjs.com] | student 3 rows in set (0.00 sec) mysql> select * from employee2; --+ | name email | job | -----+------ | Jack Smith | Jack11@abcd.com Teacher Rock Jol Rock.jol@fhro.com | cashier Andrew Stanley | ANDREW80@hfwjs.com | student +-==--- 3 rows in set (0.00 sec)

Note: if you have any queries please post a comment thanks a lot..always available to help you..