Split full name in to first, middle, and last name

Hello group!
The full name is stored in the "title" in "mytable", with some Google
job, I managed to do:

select left(title,InStr(title,' ')) AS fname,
right(title,(Length(title) -InStr(title,' '))) as lname
from mytable;

This gives me perfect result for first name, but many of the names is
the form "John L. Smith", so I got "L. Smith" for last name which is
no good.

How do I get three new fields that contains first, middle, and last
name? Also does the above syntax creates the new field in the table or
that only displays the query result?

I will need to create the corresponding result in three fields.

Thanks for help!

Jindan

Jindan Zhou wrote:
> Hello group!
> The full name is stored in the "title" in "mytable", with some Google
> job, I managed to do:
>
> select left(title,InStr(title,' ')) AS fname,
> right(title,(Length(title) -InStr(title,' '))) as lname
> from mytable;
>
> This gives me perfect result for first name, but many of the names is
> the form "John L. Smith", so I got "L. Smith" for last name which is
> no good.

SELECT SUBSTRING_INDEX(title, ' ', 1) AS fname, SUBSTRING_INDEX(title, ' ',
-1) as lname FROM mytable;

This assumes you haven't stored more information in the column.

For three columns:

SELECT SUBSTRING_INDEX(title, ' ', 1) AS fname,
SUBSTRING_INDEX(SUBSTRING_INDEX(title,' ', 2), ' ',-1) AS mname,
SUBSTRING_INDEX(title, ' ', -1) as lname FROM mytable;

There may be faster and better queries.


> How do I get three new fields that contains first, middle, and last
> name? Also does the above syntax creates the new field in the table or
> that only displays the query result?

The things you do in the select statement will only give you a result, never
create any new columns. Keep in mind this is slower than having separated
columns for first/last name.




--

//Aho

On Nov 10, 1:48 am, "J.O. Aho" <u...@example.net> wrote:
> Jindan Zhou wrote:
> > Hello group!
> > The full name is stored in the "title" in "mytable", with some Google
> > job, I managed to do:
>
> > select left(title,InStr(title,' ')) AS fname,
> > right(title,(Length(title) -InStr(title,' '))) as lname
> > from mytable;
>
> > This gives me perfect result for first name, but many of the names is
> > the form "John L. Smith", so I got "L. Smith" for last name which is
> > no good.
>
> SELECT SUBSTRING_INDEX(title, ' ', 1) AS fname, SUBSTRING_INDEX(title, ' ',
> -1) as lname FROM mytable;
>
> This assumes you haven't stored more information in the column.
>
> For three columns:
>
> SELECT SUBSTRING_INDEX(title, ' ', 1) AS fname,
> SUBSTRING_INDEX(SUBSTRING_INDEX(title,' ', 2), ' ',-1) AS mname,
> SUBSTRING_INDEX(title, ' ', -1) as lname FROM mytable;
>
> There may be faster and better queries.
>
> > How do I get three new fields that contains first, middle, and last
> > name? Also does the above syntax creates the new field in the table or
> > that only displays the query result?
>
> The things you do in the select statement will only give you a result, never
> create any new columns. Keep in mind this is slower than having separated
> columns for first/last name.
>
> --
>
> //Aho

Thank you! That gives much better result!

How do I create new column to store the them then? Since at the onset
of the design, I just used one column for the full name, now I do
want separate columns for first/middle/last name. Eventually I will
delete the full name column.

Jindan

Jindan Zhou wrote:
> On Nov 10, 1:48 am, "J.O. Aho" <u...@example.net> wrote:
>> Jindan Zhou wrote:
>>> Hello group!
>>> The full name is stored in the "title" in "mytable", with some
>>> Google job, I managed to do:
>>
>>> select left(title,InStr(title,' ')) AS fname,
>>> right(title,(Length(title) -InStr(title,' '))) as lname
>>> from mytable;
>>
>>> This gives me perfect result for first name, but many of the names
>>> is the form "John L. Smith", so I got "L. Smith" for last name
>>> which is no good.
>>
>> SELECT SUBSTRING_INDEX(title, ' ', 1) AS fname,
>> SUBSTRING_INDEX(title, ' ', -1) as lname FROM mytable;
>>
>> This assumes you haven't stored more information in the column.
>>
>> For three columns:
>>
>> SELECT SUBSTRING_INDEX(title, ' ', 1) AS fname,
>> SUBSTRING_INDEX(SUBSTRING_INDEX(title,' ', 2), ' ',-1) AS mname,
>> SUBSTRING_INDEX(title, ' ', -1) as lname FROM mytable;
>>
>> There may be faster and better queries.
>>
>>> How do I get three new fields that contains first, middle, and last
>>> name? Also does the above syntax creates the new field in the table
>>> or that only displays the query result?
>>
>> The things you do in the select statement will only give you a
>> result, never create any new columns. Keep in mind this is slower
>> than having separated columns for first/last name.
>>
>> --
>>
>> //Aho
>
> Thank you! That gives much better result!
>
> How do I create new column to store the them then? Since at the onset
> of the design, I just used one column for the full name, now I do
> want separate columns for first/middle/last name. Eventually I will
> delete the full name column.
>
> Jindan

Use an UPDATE clause, updating the new fields with the resuts of each of the
functions.

Jindan Zhou wrote:

> How do I create new column to store the them then? Since at the onset
> of the design, I just used one column for the full name, now I do
> want separate columns for first/middle/last name. Eventually I will
> delete the full name column.

ALTER TABLE
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

ALTER TABLE mytable ADD COLUMN fname VARCHAR(40);
ALTER TABLE mytable ADD COLUMN mname VARCHAR(40);
ALTER TABLE mytable ADD COLUMN lname VARCHAR(40);

UPDATE mytable SET fname=SUBSTRING_INDEX(title, ' ', 1),
mname=SUBSTRING_INDEX(SUBSTRING_INDEX(title,' ', 2), ' ',-1),
lname=SUBSTRING_INDEX(title, ' ', -1);

ALTER TABLE mytable DROP COLUMN title


Of course before you do something like this, make a backup of the table, in
case something goes wrong, you can at least restore it.


--

//Aho