Explode one column value into two column in mysql query?

Question

I want to split one column into two column and update into other two column.

Like below.

Name              FirstName      LastName
ABC NAME             ABC           NAME
PQR RNP              PQR           RNP
SUM NUM              SUM           NUM

explode and update name field value and update into same tables two column FirstName and LastName.

explode in php, create array to save into database and that's it. What's the problem? — Rahul, Aug 28 '18 at 13:21
https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql might help — aynber, Aug 28 '18 at 13:23
There is no string split function in MySQL.You'll have to create your own function. See: https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql — lofihelsinki, Aug 28 '18 at 14:09

Nick · Answer 1 · 2018-08-28T13:42:52.280

1

If you want to do this directly in MySQL, you would need the following queries:

ALTER TABLE yourtable ADD COLUMN FirstName VARCHAR(30),
                      ADD COLUMN LastName VARCHAR(30);
UPDATE yourtable 
    SET FirstName = SUBSTRING_INDEX(Name, ' ', 1),
        LastName = IF(LOCATE(' ', Name) = 0, '', SUBSTRING_INDEX(Name, ' ', -1));
ALTER TABLE yourtable DROP COLUMN Name;

Always back up your table before making structural modifications.

edited Aug 28 '18 at 13:42

answered Aug 28 '18 at 13:29

Nick

138,499
22
57
95

but what if i have single value in name like abc than as per your query it will update same name in both the field. :( – TrickStar Aug 28 '18 at 13:33
You're right - I was just going on the sample data. What do you want it to do in that scenario? make the Name the FirstName or the LastName? – Nick Aug 28 '18 at 13:34
I see you have posted an answer anyway but for future reference I have updated my answer to have the same behaviour. – Nick Aug 28 '18 at 13:43

score 0 · Answer 2 · answered Aug 28 '18 at 13:36

0

Solution :

UPDATE campaign_participants SET first_name = SUBSTRING_INDEX( name,  ' ', 1 ) ,
last_name = IF(SUBSTRING_INDEX( name,  ' ', 1 ) != SUBSTRING_INDEX( name,  ' ', -1 ), SUBSTRING_INDEX( name,  ' ', -1 ), "");

answered Aug 28 '18 at 13:36

TrickStar

229
4
19

Danon · Answer 3 · 2018-08-28T14:09:24.847

0

SELECT
  Name,
  LEFT(Name, LOCATE(' ', Name) - 2) AS FirstName,
  RIGHT(Name, LENGTH(Name) - LOCATE(' ', Name)) AS LastName
FROM
  my_table;

If you're using a different SQL engine you might want to change LENGTH() to LEN() and LOCATE() to CHARINDEX().

edited Aug 28 '18 at 14:09

answered Aug 28 '18 at 14:02

Danon

2,771
27
37

Explode one column value into two column in mysql query?

3 Answers3