TABLE1 LNAME FNAME CITY ST DOE JR JOHN PHOENIX AZ GATES BILL REDMOND WA GORE III AL BACKWOODS TN BRADSHAW TERRY HOUSTON TX
TABLE2 LNAME FNAME CITY ST DOE JOHN PHOENIX AZ GATES BILL REDMOND WA GORE AL BACKWOODS TN BRADSAW TERRY HOUSTON TX I will wnt to try to match the two together but in table one there is a suffix in the last name proceded by a space. need to get those out of there. I would match all accept for the last row -----Original Message----- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 4:13 PM To: CF-Talk Subject: RE: OT-SQL matching Can you give some sample data? Adrian Jnr Bob The Builder Mr T Are these like the data you have? You could convert to SQL and still use the concept for this: Left(str, Len(str) - Len(ListLast(str, " ")) - 1) The only difference would be how you do something like ListLast in SQL. Ade -----Original Message----- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: 23 February 2005 22:03 To: CF-Talk Subject: RE: OT-SQL matching Thanks but I am doing this in SQL where I have to do the match then I can output, Right now I am just trying to query the first table. Problem is when I do the last name if there is no suffix then I get a blank value returned, but if there is a suffix it works fine. SELECT SUBSTRING([last name],1,CHARINDEX(' ',[last name],-1)) as lastname, LEFT([first name],1) AS firstname,city,state FROM NEW_STUDENTS -----Original Message----- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 4:00 PM To: CF-Talk Subject: RE: OT-SQL matching If it's always a one word suffix then try #Left(yourQuery.yourColumn, Len(ListLast(yourQuery.yourColumn, " ") + 1))# Not tested but it looks right. Ade -----Original Message----- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: 23 February 2005 21:48 To: CF-Talk Subject: OT-SQL matching For a CF page I am doing I need to out put matched results. However in one table I have last names with a suffix like "MOLINA JR" and in another table the last name and suffix are in different columns. I need to strip out the suffixes as well as the preceding space in the first column. Any ideas. I am trying a few thing and it is not working. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196212 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
