I will give it a try. I wnet ahead and did it in two steps and got the data I want but to get it all in one would be what I would like.
-----Original Message----- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Thursday, February 24, 2005 10:44 AM To: CF-Talk Subject: RE: OT-SQL matching My bad, I misunderstood. Run this and wack in some more sample data to see if it works CREATE TABLE #temp ( Names VARCHAR(100) ) INSERT INTO #temp ( Names ) VALUES ( 'LITTLE DONALD V' ) INSERT INTO #temp ( Names ) VALUES ( 'LITTLE DONALD VICTOR' ) INSERT INTO #temp ( Names ) VALUES ( 'LITTLE DONALD Victor JR' ) SELECT '::' + SUBSTRING(Names, 1, CHARINDEX(' ', Names, 1) - 1) + '::' 'FirstName', '::' + SUBSTRING(Names, CHARINDEX(' ', Names, 1) + 1, CHARINDEX(' ', Names, CHARINDEX(' ', Names, 1)) - 1) + '::' 'LastName' FROM #temp DROP TABLE #temp I've put :: on either side to make sure no white space is being returned along with the name. Ade -----Original Message----- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: 24 February 2005 15:56 To: CF-Talk Subject: RE: OT-SQL matching It is always LASTNAME FIRSTNAME then ...... LITTLE DONALD Victor JR So I just want LASTNAME = LITTLE FIRSTNAME = DONALD -----Original Message----- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Thursday, February 24, 2005 9:55 AM To: CF-Talk Subject: RE: OT-SQL matching 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:196326 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
