[dba-SQLServer] Find the second occurrence of a character

You are just going to have to build a reference table as you go and 
validate that the name does or does not exist in the surname 
reference table.  If it does, parse it out to the surname 
column.  Then parse the balance of the name.  You will still have to 
deal with oddities like my oldest son.  Robert Christopher Lawrence 
Stewart.  But, at least only the single surname.

There are probably genealogy databases that have been built that can 
get you most of the way on the surname database.  Most likely the 
Mormons have the largest.

John, I know it was not very helpful, hence the smile.  But, I doubt 
very much that the source database that supplies your data is a 
single field.  So, getting the data in the proper format is not 
impossible.  Difficult, maybe.


Robert

At 12:00 PM 12/22/2006, you wrote:
>Date: Fri, 22 Dec 2006 09:32:17 -0500
>From: "JWColby" <jwcolby at colbyconsulting.com>
>Subject: Re: [dba-SQLServer] Find the second occurrence of a character
>         in      astring
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID: <003b01c725d5$fb9a3e80$1501a8c0 at m6805>
>Content-Type: text/plain;       charset="us-ascii"
>
>I think the "best way" to handle this if you are going to truly try to
>handle this problem is to:
>
>Develop a list of those "prefixes" to last names - Van, La, De etc.
>Take the first word as the first name
>Get a count of remaining words.
>If count > 0 then
>         ProcessRest
>Else
>         Rest is last name
>Endif
>
>ProcessRest
>         Look up the second word in the prefix list.
>         If InList then
>                 Treat everything left as the last name
>         else
>                 Treat next word as middle name
>                 remove middle name from string
>                 Process rest as last name
>         endif
>End ProcessRest
>
>Let's just say this is not s simple sql statement
>
>John W. Colby
>Colby Consulting
>www.ColbyConsulting.com
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
>artful at rogers.com
>Sent: Thursday, December 21, 2006 3:00 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] Find the second occurrence of a character in
>astring
>
>I appreciate your point, but I'm still not certain of the best way to go
>with my question, which concerns the way to handle some unusual surnames.
>
>van den Berq
>la Flame
>de la Vega
>Ben Gurion
>
>and any number of names that begin with "al". Or "da" as in Leonardo. My
>very limited Italian suggests that Leonardo was born in a town called Vinci.
>
>So how does one sort such a list? On the capitalized word? On the first
>letter of the two or three words considered the surname?
>
>Advice from Europeans, Asians, Africans, or even North Americans familiar
>with this problem, would be appreciated. I have no immediate problem that
>requires this solution. This is purely theoretical at the moment, but who
>knows, someday I may need the answer.
>
>TIA,
>Arthur
>
>----- Original Message ----
>From: Robert L. Stewart <rl_stewart at highstream.net>
>To: dba-sqlserver at databaseadvisors.com
>Sent: Thursday, December 21, 2006 1:41:04 PM
>Subject: Re: [dba-SQLServer] Find the second occurrence of a character in a
>string
>
>You put it in the right columns to begin with and don't try to parse it out
>of a single one.  :-)
>