MySQL Splitting string value separated by comma using INSERT INTO/SELECT

Question

I have a field named names in a table. This field shows "last_name, first_name". I am trying to take that field from the names table and insert the values into a new table into 2 different columns labeled last_name and first_name. I have tried the following codes:

INSERT INTO usernames (last_name,first_name)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',1),',',-1) as last_name,
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',2),',',-1) as first_name
FROM person;

and

INSERT INTO usernames (last_name,first_name)
SELECT IF(LOCATE(', ',name)>0
SUBSTRING(name,1,LOCATE(', ',name)-1) AS last_name,
IF(LOCATE(', ',name)>0
SUBSTRING(name,LOCATE(', ',name)+1),NULL) AS first_name
FROM person;

I'm not sure what I am doing wrong with this code. Any help Is greatly appreciated.

Thank you, Larry

Inna Tichman · Accepted Answer · 2017-08-07 21:18:35Z

1

your query is almost ok, but second SELECT is not needed. check the syntax of SELECT in documentation.

INSERT INTO usernames (last_name,first_name)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',1),',',-1) as last_name,
SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',2),',',-1) as first_name
FROM person;

answered Aug 7, 2017 at 21:18

Inna Tichman

6266 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Larry Over a year ago

Thank you for the quick response Inna Tichman. That worked beautifully

Collectives™ on Stack Overflow

MySQL Splitting string value separated by comma using INSERT INTO/SELECT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related