Split value from one field to two
SELECT variant (not creating a user defined function):
SELECT IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
`membername`
) AS memberfirst,
IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
NULL
) AS memberlast
FROM `user`;
This approach also takes care of:
- membername values without a space: it will add the whole string to memberfirst and sets memberlast to NULL.
- membername values that have multiple spaces: it will add everything before the first space to memberfirst and the remainder (including additional spaces) to memberlast.
The UPDATE version would be:
UPDATE `user` SET
`memberfirst` = IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
`membername`
),
`memberlast` = IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
NULL
);
It seems that existing responses are over complicated or not a strict answer to the particular question.
I think, the simple answer is the following query:
SELECT
SUBSTRING_INDEX(`membername`, ' ', 1) AS `memberfirst`,
SUBSTRING_INDEX(`membername`, ' ', -1) AS `memberlast`
;
I think it is not necessary to deal with more-than-two-word names in this particular situation. If you want to do it properly, splitting can be very hard or even impossible in some cases:
- Johann Sebastian Bach
- Johann Wolfgang von Goethe
- Edgar Allan Poe
- Jakob Ludwig Felix Mendelssohn-Bartholdy
- Petőfi Sándor
- Virág Vendelné Farkas Margit
- 黒澤 明
In a properly designed database, human names should be stored both in parts and in whole. This is not always possible, of course.
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:
DELIMITER $$
CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
END$$
DELIMITER ;
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;