MySQL split value from one field to two

Question

I've got a table field membername which contains both the last name and the first name of users. Is it possible to split those into 2 fields memberfirst, memberlast?

All the records have this format "Firstname Lastname" (without quotes and a space in between).

Answer 1

Unfortunately MySQL does not feature a split string function. However you can create a user defined function for this, such as the one described in the following article:

• MySQL Split String Function by Federico Cargnelutti

With that function, you would be able to build your query as follows:

SELECT SPLIT_STR(membername, ' ', 1) as memberfirst,
       SPLIT_STR(membername, ' ', 2) as memberlast
FROM   users;

If you prefer not to use a user defined function and you do not mind the query to be a bit more verbose, you can also do the following:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst,
       SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast
FROM   users;

Answer 2

The only case where you may want such a function is an UPDATE query which will alter your table to store Firstname and Lastname into separate fields.

Database design must follow certain rules, and Database Normalization is among most important ones

Answer 3

use this

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', 2 ),' ',1) AS b, 
SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', -1 ),' ',2) AS c FROM `users` WHERE `userid`='1'