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

JWColby jwcolby at colbyconsulting.com
Fri Dec 22 08:32:17 CST 2006

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
	Rest is last name

	Look up the second word in the prefix list.  
	If InList then
		Treat everything left as the last name
		Treat next word as middle name
		remove middle name from string
		Process rest as last name
End ProcessRest

Let's just say this is not s simple sql statement

John W. Colby
Colby Consulting

-----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

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.


----- 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

You put it in the right columns to begin with and don't try to parse it out
of a single one.  :-)

dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com

More information about the dba-SQLServer mailing list