Way off the wall from left field but can you use LIKE between fields. Something such as this:
NameWithSuffixField LIKE NameOnlyField% Is that allowed in SQL? -------------- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning ....-----Original Message----- ....From: Eric Creese [mailto:[EMAIL PROTECTED] ....Sent: Wednesday, February 23, 2005 2:58 PM ....To: CF-Talk ....Subject: RE: OT-SQL matching .... ....hmmmmm not sure unless I give it a try. .... ....Here is my revised SQL, I got rid of the substring on the last name and ....went with matching the first three letters .... ....SELECT ....n.[last name] as lastname,x.perlname, ....n.[first name] as firstname,x.perfname, ....n.[street 1],x.addrline1,n.city,n.state ,x.city,x.state ....FROM NEW_STUDENTS n, EAA_CHECK x ....WHERE LEFT(n.[first name],1) = left(x.perfname,1) ....AND n.city =x.city ....AND n.state = x.state ....AND LEFT(n.[street 1],3)= left(x.addrline1, 3) ....AND LEFT(n.[last name],3) = left(x.perlname,3) .... ....-----Original Message----- ....From: Adrian Lynch [mailto:[EMAIL PROTECTED] ....Sent: Wednesday, February 23, 2005 4:30 PM ....To: CF-Talk ....Subject: RE: OT-SQL matching .... .... ....Here's something I got from the web, so not claiming credit for the ....function. .... ..../* .... SELECT * FROM Split('Hella:Cool', ':') ....*/ .... ....CREATE FUNCTION Split( .... .... @string NVARCHAR(4000), @delimiter CHAR(1) .... ....) RETURNS @results TABLE( [ID] INT IDENTITY(1,1), Items NVARCHAR(4000) ) .... ....AS .... ....BEGIN .... .... DECLARE .... @index INT, .... @slice NVARCHAR(4000) .... .... SET @index = 1 .... .... IF @string IS NULL RETURN .... .... WHILE ( @index != 0 ) BEGIN .... .... SELECT @index = CHARINDEX(@delimiter,@string) .... .... IF ( @index != 0 ) .... SELECT @slice = LEFT(@string, @index - 1) .... ELSE .... SELECT @slice = @string .... .... INSERT INTO @results ( .... Items .... ) VALUES ( .... @slice .... ) .... .... SELECT @string = RIGHT(@string,LEN(@string) - @index) .... .... IF LEN(@string) = 0 .... BREAK .... .... END .... .... RETURN .... ....END .... ....It returns a table so you can select the first part of the name. .... ....Is that any help? .... ....Ade .... ....-----Original Message----- ....From: Eric Creese [mailto:[EMAIL PROTECTED] ....Sent: 23 February 2005 22:22 ....To: CF-Talk ....Subject: RE: OT-SQL matching .... .... ....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 .... ....-- ....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:196229 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
