Split value from one field to two

Question

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;

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.

If your plan is to do this as part of a query, please don't do that ^(a). Seriously, it's a performance killer. There may be situations where you don't care about performance (such as one-off migration jobs to split the fields allowing better performance in future) but, if you're doing this regularly for anything other than a mickey-mouse database, you're wasting resources.

If you ever find yourself having to process only part of a column in some way, your DB design is flawed. It may well work okay on a home address book or recipe application or any of myriad other small databases but it will not be scalable to "real" systems.

Store the components of the name in separate columns. It's almost invariably a lot faster to join columns together with a simple concatenation (when you need the full name) than it is to split them apart with a character search.

If, for some reason you cannot split the field, at least put in the extra columns and use an insert/update trigger to populate them. While not 3NF, this will guarantee that the data is still consistent and will massively speed up your queries. You could also ensure that the extra columns are lower-cased (and indexed if you're searching on them) at the same time so as to not have to fiddle around with case issues.

And, if you cannot even add the columns and triggers, be aware (and make your client aware, if it's for a client) that it is not scalable.

---

^(a) Of course, if your intent is to use this query to fix the schema so that the names are placed into separate columns in the table rather than the query, I'd consider that to be a valid use. But I reiterate, doing it in the query is not really a good idea.

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'