f



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

0
jindan (50)
11/10/2007 7:14:50 AM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

4 Replies
6136 Views

Similar Articles

[PageSpeed] 24

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
0
J
11/10/2007 7:48:53 AM
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

0
Jindan
11/10/2007 3:18:30 PM
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. 


0
Paul
11/10/2007 8:51:06 PM
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
0
J
11/10/2007 9:19:50 PM
Reply:

Similar Artilces:

Re: How to break out Patient Full Name into Last, First and Middle Name
You will have to use the SCAN FUNCTION. Last= Scan(patient_nm,1,','); first= Scan(patient_nm,2,','); MI= Scan(patient_nm,3,','); The commas will be the delimiters . -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of s huynh Sent: Wednesday, November 17, 2004 12:53 PM To: SAS-L@LISTSERV.UGA.EDU Subject: How to break out Patient Full Name into Last, First and Middle Name Hi, Can anyone help me separate the following PATIENT_NM variable into FIRST_NM, LAST_NM, and MIDDLE_NM fields? I wan...

Re: How to break out Patient Full Name into Last, First and Middl e Name (#2004-1504)
Max, What version of SAS are you using? Is parsename part of base SAS or ?? thanks Dave -----Original Message----- From: Worlund, Max [mailto:ZQY3@CDC.GOV] Sent: Wednesday, November 17, 2004 1:22 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: How to break out Patient Full Name into Last, First and Middle Name (#2004-1504) Try CALL PARSENAME. Define the name variables you want out of it and viola, it will parse the names. I believe if you don't want Prefix and Suffix or Appendage, just leave them out. NAME_PREFIX = ' '; FIRST_NAME= ' '; MIDDLE_NAME= ' '; LAST_NAME=...

Re: group by first name or last name
How about John Smith and Elton John? Same group? On Tue, 17 Aug 2004 08:50:59 -0700, Art <artshen@HOTMAIL.COM> wrote: >How to group peole by their first name OR last name, > >1 John Smith >2 Goerge Smith >3 Bill Clinton >4 George Bush > > >I need to put 1,2,4 into to one group. Is there any way I can do this >in SAS or SQL? > >Art ...

Re: group by first name or last name #4
Art: The spelling error in your sample data highlights one of the problems with grouping as a means of clustering data. The more basic complications introduced by OR conditions (disjunctions) generally affect query accuracy and performance. A fundamental problem in disjunct groups is that disjunctive queries (WHERE x OR z) do not yield disjuct sets. In terms relevant to x OR z groups, a row for a person can belong to either the first name group, or the last name group, or both. Grouping on x or z also tends to complicate search strategies and, as a result, query performance. Grouping by firs...

Re: group by first name or last name #5
Probably obvious in context, but I meant to type 'disjunct set', not 'disjuct set' .... Sig -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Sigurd Hermansen Sent: Thursday, August 19, 2004 3:06 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: group by first name or last name Art: The spelling error in your sample data highlights one of the problems with grouping as a means of clustering data. The more basic complications introduced by OR conditions (disjunctions) generally affect query accuracy and performance. A fundamental problem...

Combo box on form won;t fill in second record where last name is same; uses data from first record of same last name
I have a combo box on a form that shows several columns...first name, last name etc. It is based on a query and pulls from a "one" table. The records in the "one" table need to be unique based on first and last names. In other words, only one "Tim Allen". The form is also attached to a "many" table. The problem is I can fill in the form data when I pick "Tim Allen" the first record with last name of Allen, BUT if I have another record with the same last name but different first name (i.e. Karen Allen), when I select Karen Allen it fills in dat...

Typical length of first name, last name, address field in the US?
Are there standards, and what are they? Thanks. Matchy wrote: > Are there standards, and what are they? Thanks. I have a good sized database which contains names: mysql> SELECT COUNT(*),AVG(LENGTH(firstname)) FROM cand WHERE 1; +----------+------------------------+ | COUNT(*) | AVG(LENGTH(firstname)) | +----------+------------------------+ | 439577 | 5.9249 | +----------+------------------------+ 1 row in set (0.24 sec) mysql> SELECT COUNT(*),AVG(LENGTH(lastname)) FROM cand WHERE 1; +----------+-----------------------+ | COUNT(*) | AVG(LENGTH(lastname)) | +-...

The database name component of the object qualifier must be the name of the current database
Guys, 1st of all.. sorry about my english ok? Im from Brazil... Im trying to get a RS connecting through 2 databases with the query below: SELECT scar..osbalcao.numeroos, ccell.dbo.atrib_os.os FROM ccell.dbo.atrib_os JOIN scar.dbo.osbalcao on ccell.dbo.atrib_os.os = scar.dbo.osbalcao.numeroos WHERE scar.dbo.osbalcao.localprod = '257' AND scar.dbo.osbalcao.status in ('EM') On a SQL IDE i got a good RS, but, when i try to use that same query on Delphi + ZEOS, the SQL Server tells me that "The database name component of the object qualifier must be the name of the current...

Invoking a function using full package name fail, but works without full package name
Hello; In my code, I usually like to add the package name to the function name to know where each function is comming from. (A sort of self documenting the code) Now I found this to cause a problem with this Mathematica package. This is an example << ImageProcessing` img = ImageProcessing`ImageRead["test.gif"]; Show[Graphics[img]]; The above gives an error Graphics::gprim: ImageProcessing`ImageRead[test.gif] was encountered \ where a Graphics primitive or directive was expected BUT when I do this: img = ImageRead["test.gif"]; Show[Graphi...

Bibliography style like "plain" but with "Surname, First Name" instead of "First Name, Surname"
Hello, is there any numbered bibliography style like the "plain" one but such that it lists authors' surnames before their first names? I tried many styles, however, I was not able to find any such one. Any help would be greatly appreciated. Best regards, vt ...

What is the name of the name space I am in?
I try to use "new.new.classobj (name, baseclass, dict)" and have no clue what the "dict" of the current name space is. I can name dicts of imported modules, because their name exists in the current name space. If, for instance, I import a module "service" then that module's name space would be "service.__dict__". But if I import * from service, then I incorporate that name space into the current one and I cannot name it, because the current module's name is not part of the module's own name space. "dir (service)" is e...

Determine Database Name or Instance Name from SQL in DB2
Is it possible with a SQL query against a system table to determine what the database name or instance name is that you are logged into? We are on DB2 8.1.6. Spencer I'd look at the snapshot table functions for this.. Just a guess. Cheers Serge Spencer wrote: > Is it possible with a SQL query against a system table to determine > what the database name or instance name is that you are logged into? > We are on DB2 8.1.6. > Spencer In SQL, you can use 'CURRENT SERVER' special register to return the name of the database you're connected to, as in...

save name name
Dear all, I came across code, and have seen this: save me me save EXCHCD EXCHCD save SIC SIC save PRC PRC I wonder why the variable name occurs twice, does anybody know why that is? Many thanks in advance! Chris On 4/21/2015 2:28 AM, Christoph Meier wrote: > Dear all, > I came across code, and have seen this: > > save me me > save EXCHCD EXCHCD > save SIC SIC > save PRC PRC > > I wonder why the variable name occurs twice, does anybody know why that is? > > Many thanks in advance! > > Chris > one name is the variable n...

What is the Name of #? How did # Get its Name?
Julian Macassey wrote to the Digest fifteen years ago and asked about the ' # ' key on telephones. There were several messages on it at the time, then about nine years ago, in 1995 we had a more or less definitive answer. For those of you who have wondered about this key which is located directly below the 9 on telephone keypads, here are the answers we printed at the time, as per our archives. Subject: Octothorpe source Date: 19 Nov 88 15:25:08 PST (Sat) From: ucla-an!bongo!julian@ee.UCLA.EDU (julian macassey) I am looking for an authoritative reference for the term OCTOTHORPE. An octothorpe is an # , which is what is usually referred to as "the pound sign" or "the hash mark", sometimes as "the number symbol". I know the correct term is octothorpe, I have seen references to it in some Bell docs, I have even seen a news clipping years ago that mentioned it. My problem is that every now and again, some smart Alec asks me where it comes from. I have even been accused of making it up. No dictionary I have seen has ever given me a definition. Yes I have looked it up in the 24 Volume Oxford English Dictionary. I have checked the encyc Brit and alleged Telecommunications Dictionaries. I do know that Octo means eight and Thorpe means beam. So the word has some roots. There is a good term paper here for someone. Julian Macassey, n6are julian@bongo voice (213) 653-4495 ...

iSOFT names no names
bigges fraudsters in human history --- CSC and now iSOFT ...

What version of LabVIEW first included Bundle by Name and Unbundle by Name?
What version of LabVIEW first included Bundle by Name and Unbundle by Name? &nbsp; Allen Weekley LabVIEW 3.0 does have Bundle By Name and Unbundle By Name. I'm curious why you need to know this. -D I&nbsp;am auditing software that was originally written a long time ago by someone who is long gone from the company. In a discussion of this, someone offered the theory that Unbundle by Name did not exist in LabVIEW 4.0 as an excuse for one aspect of the poor programming style. I couldn't remember for sure if LabVIEW 3.0 had Unbundle by Name. The question was merely academic. Ther...

How to split file name and the path name (was: Re: wxMenu and wxUpdateUIEvent)
Sorry, send under the wrong subject line.... -----Original Message----- >From: Igor Korot <ikorot@earthlink.net> >Sent: Feb 5, 2008 10:06 PM >To: wx-users@lists.wxwidgets.org, wx-users@lists.wxwindows.org >Subject: Re: wxMenu and wxUpdateUIEvent > >Hi, ALL, >Is there a way to get a file name only from the complete >path that is represented by string? Something like, e.g.: > >"test.doc", > >out of > >"C:\Texts\test.doc". > >Problem is I need to keep the original file name, and thought >that there i...

Last name first et al
I have a file with name field data entered last name first and sorted that way. How can I change all those names to first name first? Because I need to import from another file that is entered first name first. After the import I don't really care how it's displayed but I would still like it sorted last name first. Thank You In Advance. In article <1gsaa3w.1g8ago6iv84o8N%elvisp@compuserve.com>, elvisp@compuserve.com (The Wolf) wrote: > I have a file with name field data entered last name first and sorted > that way. > > How can I change all those names to first...

Formal name for the (Mc,Mac,O,Van...) part of a last name?
Is there a formal name for the (Mc,Mac,O,Van...) part of a last name? Tom Yes, there is. It is part of the last name and there are not spaces. MCDONALD. -- Dean Covey www.coveyaccounting.com MS-Office Certified: http://www.microsoft.com/learning/mcp/officespecialist/default.asp "Tom Warren" <tww@gate.net> wrote in message news:1106183290.320811.268300@f14g2000cwb.googlegroups.com... > Is there a formal name for the (Mc,Mac,O,Van...) part of a last name? > Tom > "DC" <nospam> wrote: >Yes, there is. It is part of the last name and ther...

Re: How to split file name and the path name (was: Re: wxMenu and wxUpdateUIEvent)
Thank you. -----Original Message----- >From: SAM <nospam@i.net> >Sent: Feb 5, 2008 11:33 PM >To: wx-users@lists.wxwidgets.org >Subject: Re: How to split file name and the path name (was: Re: wxMenu and wxUpdateUIEvent) > > >news:21751119.1202270814353.JavaMail.root@elwamui-muscovy.atl.sa.earthlink.net... >> Sorry, send under the wrong subject line.... >> >> -----Original Message----- >>>From: Igor Korot <> >>>Sent: Feb 5, 2008 10:06 PM >>>To: wx-users@lists.wxwidgets.org, wx-users@lists.wxwindows.org ...

rename all the file names in a directory so that the first character of each file name is lower case
How to rename all the file names in a directory so that the first character of each file name is lower case in the bash shell? e.g. old file names are: Certificates General Util.dsp new file names become: certificates general util.dsp On 2008-09-07, TsanChung wrote: > How to rename all the file names in a directory so that the first > character of each file name is lower case in the bash shell? > e.g. > old file names are: > Certificates > General > Util.dsp > > new file names become: > certificates > general > util.dsp _lwr() { _LWR= case $1 in ...

Mail.app: Last name always first?
Hello, Please correct me if I'm wrong, but prior to Panther, the outgoing recipients' email addresses appeared first name first, not last name first in an outgoing e-mail. Now, it appears that the last name comes first, which I find unappealing, especially for friends. (I'd prefer "John Smith" to "Smith John".) Is there any way to switch this back? Thanks, Todd In article <fa382483-0CAA23.08053605122003@news.skynet.be>, "F. Todd Wilson" <fa382483@nospam.skynet.be> wrote: > Please correct me if I'm wrong...

scope of $name in: (my $name = 123) && something-involving-$name;
I find it strange in the examlpe below 1 open(my $fh, my $name="</no/such/file") || 2 warn "Error name=$name\n"; 3 print "name=$name"; that $name is not in the scope, at line 2 (in the warn() arg). The assignment to $name in line 1 definitely already happened. So how come $name is not known at line 2 ? Is this a feature or a bug ? What is the explanation ? Does perl, like, wait for the semicolon to add $name to the hashtable or variables ? This is counterintuitive to me ... (comparing to C foo() { int x=1, y=x; } ) Thanks Y...

Use association and clashing generic names/type names/procedure names
There are restrictions on clashing local identifiers in a scope and use of clashing identifiers introduced through use association. In recent standards aspects of the rules for local identifiers in a scope have been relaxed to permit a generic name to be the same as a procedure or type. Aspects of the current rules read as if the relaxation is perhaps not applicable to the use association restrictions. So... is the following conforming? MODULE a IMPLICIT NONE PRIVATE PUBLIC :: Proc INTERFACE Proc PROCEDURE proc_a END INTERFACE Proc CONTAINS SUBROUTINE proc_a(arg) INTEGER, INTENT(IN) :: arg END SUBROUTINE proc_a END MODULE a MODULE b IMPLICIT NONE PRIVATE PUBLIC :: Proc CONTAINS SUBROUTINE Proc(arg) REAL, INTENT(IN) :: arg END SUBROUTINE Proc END MODULE b PROGRAM c USE a USE b IMPLICIT NONE INTERFACE Proc PROCEDURE Proc END INTERFACE Proc END PROGRAM c How about this? MODULE x IMPLICIT NONE PRIVATE PUBLIC :: Proc INTERFACE Proc PROCEDURE proc_x END INTERFACE Proc CONTAINS FUNCTION proc_x(arg) INTEGER, INTENT(IN) :: arg INTEGER :: proc_a proc_a = arg + 1 END FUNCTION proc_x END MODULE x MODULE y IMPLICIT NONE PRIVATE PUBLIC :: Proc TYPE :: Proc END TYPE Proc END MOD...

Web resources about - Split full name in to first, middle, and last name - comp.databases.mysql

Security Middle East - Latest news from the Middle East.
Security Middle East is a news portal for the entire security industry, focussed specifically on latest security news from the Middle East. Security ...

American middle class - Wikipedia, the free encyclopedia
contemporary social scientists have put forward several, more or less congruent, theories on the American middle class. Depending on class model ...

Turnbull and the politics of Malcolm in the middle
When Malcolm Turnbull swept into the prime ministership, he promised us a fresh start. Gone were the days when governments would rule things ...

Middle-aged Australians drive rise in national suicide rate
A steep rise in death by suicide among middle-aged Australians and young women has driven the national suicide rate to its highest level in 13 ...

Fact check: Is the proposed Shenhua Watermark coal mine located in the middle of Australia's best agricultural ...
Is the proposed Shenhua Watermark coal mine on the Liverpool Plains located in the middle of Australia's best agricultural land?

Third incident of anti-Semitic graffiti found at Newton middle school - The Boston Globe Third incident ...
Brian Turner, principal of the F.A. Day Middle School, apologized to parents Wednesday night for failing to report the first two incidents in ...

Stuck In The Middle, Merkel’s Candidates Lose Out On Left And Right Votes, Fail To Retake States
Stuck In The Middle, Merkel's Candidates Lose Out On Left And Right Votes, Fail To Retake States

Congress to vote on labelling Christian persecution in the Middle East 'genocide', as pressure on Obama ...
The US Congress will on Monday decide whether to recognise atrocities committed against Christians and other religious minorities as genocide. ...

Perkins: What’s Happening to Christians in Middle East 'Meets UN’s Technical Definition of Genocide to ...
The slaughter of Christians in the Middle East “meets the United Nations’ technical definition [of genocide] to a T,” Family Research Council ...

All four remaining Republican candidates want to go to war in the Middle East again
... candidate on stage said he would. Because if there's anything the conservative movement has learned about military adventures in the Middle ...

Resources last updated: 3/15/2016 11:03:17 AM