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

User · Answer

In case someone needs to run over a table and split a field:

First we use the function mention above:

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_split_str`($str VARCHAR(800), $delimiter VARCHAR(12), $position INT) RETURNS varchar(800) CHARSET utf8
    DETERMINISTIC
BEGIN 
    RETURN REPLACE(
            SUBSTRING(
                SUBSTRING_INDEX($str, $delimiter, $position),
                LENGTH(
                    SUBSTRING_INDEX($str, $delimiter, $position -1)
                ) + 1
            ),
    $delimiter, '');
END

Second, we run in a while loop on the string until there isn't any results (I've added $id for JOIN clause):

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_split_str_to_rows`($id INT, $str VARCHAR(800), $delimiter VARCHAR(12), $empty_table BIT) RETURNS int(11)
BEGIN

    DECLARE position INT;
    DECLARE val VARCHAR(800);
    SET position = 1;
    
    IF $empty_table THEN
        DROP TEMPORARY TABLE IF EXISTS tmp_rows;    
    END IF;
            
    SET val = fn_split_str($str, ',', position);
            
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_rows AS (SELECT $id as id, val as val where 1 = 2);
        
    WHILE (val IS NOT NULL and val != '') DO               
        INSERT INTO tmp_rows
        SELECT $id, val;
        
        SET position = position + 1;
        SET val = fn_split_str($str, ',', position);
    END WHILE;
    
    RETURN position - 1;
END

Finally we can use it like that:

DROP TEMPORARY TABLE IF EXISTS tmp_rows;
SELECT  SUM(fn_split_str_to_rows(ID, FieldToSplit, ',', 0))
FROM    MyTable;

SELECT * FROM tmp_rows;

You can use the id to join to other table.

In case you are only splitting one value you can use it like that

SELECT  fn_split_str_to_rows(null, 'AAA,BBB,CCC,DDD,EEE,FFF,GGG', ',', 1);
SELECT * FROM tmp_rows;

We don't need to empty the temporary table, the function will take care of that.

User · Answer

mysql 5 4 provides a native split function   SPLIT STR  lt column gt     lt delimiter gt     lt index gt

User · Answer

Not exactly answering the question  but faced with the same problem I ended up doing this   UPDATE people exit SET last name   SUBSTRING INDEX fullname     -1  UPDATE people exit SET middle name   TRIM SUBSTRING INDEX SUBSTRING INDEX fullname last name 1      -2   UPDATE people exit SET middle name      WHERE CHAR LENGTH middle name  gt 3  UPDATE people exit SET first name   SUBSTRING INDEX fullname concat middle name     last name  1  UPDATE people exit SET first name   middle name WHERE first name      UPDATE people exit SET middle name      WHERE first name   middle name

User · Answer

UPDATE  salary generation tbl  SET      modified by    IF          LOCATE       other salary string    gt  0          SUBSTRING  other salary string   1  LOCATE       other salary string   - 1            other salary string              other salary    IF          LOCATE       other salary string    gt  0          SUBSTRING  other salary string   LOCATE       other salary string     1           NULL

User · Answer

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

User · Answer

Method I used to split first name into first name and last name when the data arrived all in the first name field  This will put only the last word in the last name field  so  john phillips sousa  will be  john phillips  first name and  sousa  last name  It also avoids overwriting any records that have been fixed already   set last name trim SUBSTRING INDEX first name       -1    first name trim SUBSTRING first name 1 length first name  - length SUBSTRING INDEX first name       -1     where list id   List ID  and length first name  gt 0 and length trim last name   0

User · Answer

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

User · Answer

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

User · Answer

SELECT variant  not creating a user defined function    SELECT IF          LOCATE       membername    gt  0          SUBSTRING  membername   1  LOCATE       membername   - 1            membername        AS memberfirst      IF          LOCATE       membername    gt  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    gt  0          SUBSTRING  membername   1  LOCATE       membername   - 1            membername              memberlast    IF          LOCATE       membername    gt  0          SUBSTRING  membername   LOCATE       membername     1           NULL

User · Answer

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 Petofi S  ndor        In a properly designed database  human names should be stored both in parts and in whole  This is not always possible  of course

User · Answer

In MySQL this is working this option   SELECT Substring nameandsurname  1  Locate      nameandsurname  - 1  AS         firstname          Substring nameandsurname  Locate      nameandsurname    1     AS lastname  FROM   emp

User · Answer

This takes smhg from here and curt s from Last index of a given substring in MySQL and combines them  This is for mysql  all I needed was to get a decent split of name to first name last name with the last name a single word  the first name everything before that single word  where the name could be null  1 word  2 words  or more than 2 words  Ie  Null  Mary  Mary Smith  Mary A  Smith  Mary Sue Ellen Smith   So if name is one word or null  last name is null  If name is   1 word  last name is last word  and first name all words before last word   Note that I ve already trimmed off stuff like Joe Smith Jr    Joe Smith Esq  and so on  manually  which was painful  of course  but it was small enough to do that  so you want to make sure to really look at the data in the name field before deciding which method to use    Note that this also trims the outcome  so you don t end up with spaces in front of or after the names   I m just posting this for others who might google their way here looking for what I needed  This works  of course  test it with the select first    It s a one time thing  so I don t care about efficiency   SELECT TRIM       IF          LOCATE       name    gt  0          LEFT  name   LENGTH  name   - LOCATE      REVERSE  name               name           AS first name  TRIM       IF          LOCATE       name    gt  0          SUBSTRING INDEX  name        -1            NULL          AS last name FROM  users     UPDATE  users  SET  first name    TRIM       IF          LOCATE       name    gt  0          LEFT  name   LENGTH  name   - LOCATE      REVERSE  name               name             last name    TRIM       IF          LOCATE       name    gt  0          SUBSTRING INDEX  name        -1            NULL

User · Answer

I had a column where the first and last name were both were in one column  The first and last name were separated by a comma  The code below worked  There is NO error checking correction  Just a dumb split  Used phpMyAdmin to execute the SQL statement   UPDATE tblAuthorList SET AuthorFirst   SUBSTRING INDEX AuthorLast     -1    AuthorLast   SUBSTRING INDEX AuthorLast     1     13 2 10 UPDATE Syntax

User · Answer

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

[mysql] Split value from one field to two

Examples related to mysql

Examples related to split