JWColby
jwcolby at colbyconsulting.com
Wed Dec 20 19:54:28 CST 2006
I sidestepped it because it is not trivial. I did a system in Access and ended up using a name lookup. There are all kinds of issues, just in European names. Take the Micks and Macks, O'donnely etc. Sometimes they are right next to each other, sometimes space separated. Spanish and french have their own issues. Spanish is a true mess due to the fact that the women often take the husband's name as their MIDDLE name keeping their MATERNAL family name (IIRC) as their last name. I mean you have to KNOW THE RULES to sort that kind of stuff out, and then you have to know the language that the name is. AFAICT, the professional systems also resort to name lookups in at least some cases. I ended up finding a large list of (correctly spelled) names, then running all of the names in a huge database of mine through that list, then taking the ones that fell out, visually checking them, and placing valid ones in the lookup table. 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: Wednesday, December 20, 2006 7:52 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Find the second occurrence of a character inastring You sidestepped the important issue, JC (well important in some contexts), which is how does one sort this? A. ----- Original Message ---- From: JWColby <jwcolby at colbyconsulting.com> To: dba-sqlserver at databaseadvisors.com Sent: Wednesday, December 20, 2006 4:05:08 AM Subject: Re: [dba-SQLServer] Find the second occurrence of a character in astring For Spanish names even that does not work, although I think the difference is not worth fighting for (but I'm not Spanish). Vega is not the last name. De La Vega is the last name. De la means "of the" as in Of The Vega. Mi amigo in Mexico es Juan De Uriarte, Juan being the first name, De Uriarte being the Paternal family name. 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: Tuesday, December 19, 2006 9:28 PM To: dba-sqlserver at databaseadvisors.com Cc: Peter Brawley Subject: [dba-SQLServer] Find the second occurrence of a character in astring Assume these two values in two rows in the table of interest: Andrei Pascal Arthur B. Fuller I need to parse out the surname. I can't just grab the characters following the first space, unless there is only one space. But if there are two spaces, then I need the trailing string following the second space. CHARINDEX( 'f', 'abcdefgh' ) returns 6 so that part is easy. How to find the second occurrence, if any? In fact, assuming really bizarre data, what I want is the string following the last space in the value. Even that may not work, given Portuguese and Spanish and French surnames, but for now I will live with that. Suppose an entry such as: Juan Carlos de la Vega I need to grab the string following the last space, in T-SQL syntax. Any ideas? TIA, Arthur _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com