In fact I think you're on to a hiding to nothing... .... I don't think you can make any reasonable assumptions about what is a middle name and what is a suffix. In the before samples VICTOR could be a suffix.
Maybe I'm not understanding it all but I don't think you'll be able to do this with 100% accuracy. Ade -----Original Message----- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: 24 February 2005 15:49 To: CF-Talk Subject: RE: OT-SQL matching Will the last name always be first in the column? In the samples below, is the person's first name DONALD, and then last names LITTLE, LITTLE VICTOR and LITTLE Victor? LITTLE DONALD V LITTLE DONALD VICTOR LITTLE DONALD Victor JR Ade -----Original Message----- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: 24 February 2005 15:37 To: CF-Talk Subject: RE: OT-SQL matching Okay I go something to work so my orginal question is taken care of. However I have another question that deals with this sort of thing. I am now looking at another datasource that I am going to have to match on at somepoint later but they have a single name field where a persons name is one field and is LASTNAME FIRSTNAME, MIDDLE SUFFIX (LITTLE DONALD V or LITTLE DONALD VICTOR or LITTLE DONALD Victor JR). I am simply querying out the information that I need and putting it in a holding table. But while I am doing this I want to extract the LAST and FIRST names and store them in their seperate columns in the holding the table. I can get the last name and I can get the first name but along with the first name I get the middle name and suffix as well. By the way this is a data file provided by the FAA. Here is what I am doing lease the INTO statement. SELECT SUBSTRING([name], 1, CHARINDEX(' ', [name])) AS lastname, SUBSTRING([name], CHARINDEX(' ',[name])+1,LEN([name])) as firstname , street,street2,city,state,[zip code] as zip FROM master ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196304 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
