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.