List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 6 2001 10:04pm
Subject:Re: LOAD DATA INFILE - "split" one field into two??
View as plain text  
>I have a tab delimited file from a spreadsheet that
>has a field "Name" (includes first and last name).
>I am importing the file into a table that has
>"firstName" and "lastName" as seperate fields.
>Is there a way to split the field from the text file
>"Name" into two different fields in the database,
>"firstName" and "lastName"?
>I would assume the last word in "Name" is the last
>name for "lastName" in mysql table.
>Anthony Ettinger

That's ugly.  You can't do it with LOAD DATA, but if your first-name
or last-name fields are long enough to hold the entire name, you can
load the values into one of those columns and then split the name
afterward.  Assuming there is exactly one space in a name, and assuming
the names are loaded into last_name, you can do this:

UPDATE tbl_name SET first_name = SUBSTRING_INDEX(last_name,' ',1);
UPDATE tbl_name SET last_name = SUBSTRING_INDEX(last_name,' ',-1);

Here's an example of how this works:

mysql> SET @last_name = "Paul Revere";
mysql> SET @first_name = SUBSTRING_INDEX(@last_name,' ',1);
mysql> SET @last_name = SUBSTRING_INDEX(@last_name,' ',-1);
mysql> SELECT @first_name, @last_name;
| @first_name | @last_name |
| Paul        | Revere     |

Paul DuBois, paul@stripped
LOAD DATA INFILE - "split" one field into two??Anthony E.6 Sep
  • Re: LOAD DATA INFILE - "split" one field into two??Marjolein Katsma6 Sep
  • Re: LOAD DATA INFILE - "split" one field into two??Paul DuBois6 Sep