Chris, if I understand correctly you are looking for a loop in SQL, going from the most-specific case to the most general. I'm about to drop off in slumber so I may just be delerious, but something like this might work: declare @i tinyint, @txt varchar(20), @result varchar(20) set @txt = '447786595432' /* user-inputted value, trim it to 20 chars or less */ set @i = len(@txt) while @i>=0 begin set @result = left(@txt, at i) if ( select count(*) from [tablename] where TelephoneCode like @result+'%')>0 break else set @i = @i - 1 end select @result as [mycode] You might add a little if...else at the end if you want to avoid returning a null value. You might also tweak it a bit to avoid a one-character match (i.e. a result of just '4' would be invalid). Or, use a simple condition in the ASP code to do the same. Maximillian Von Schwanekamp Enabling microbusiness and infopreneurs NeptuneWebworks.com :: InfoMarketingInsider.net voice: 541-302-1438 fax: 208-730-6504 -----Original Message----- From: Jason Handby [mailto:jason_handby at illustraresearch.com] Sent: Tuesday, February 17, 2004 2:17 AM To: chris at martiantechnologies.com Cc: thelist at lists.evolt.org Subject: RE: RE: [thelist] SQL - best match Hi Chris, > > If you want the best match (by which I guess you mean the > longest code > > that begins '44') then you could do this: > > > > SELECT TOP 1 * FROM MyTable WHERE TelephoneCode LIKE > '44%' ORDER BY > > LEN(TelephoneCode) DESC > > Thanks for the reply. This is sort of what I want to do, but > not quite. The user inputs an entire phone number, eg. > 447786595432. I then want to see what the logest code in the > database that will match this number is. Thus, if the code > 447786 exists in the database, I don't want to return 44, > 4477 or 4478; I want to return 447786. Oh, right, I see. In which case you could try SELECT TOP 1 * FROM MyTable WHERE '447786595432' LIKE (TelephoneCode + '%') ORDER BY LEN(TelephoneCode) DESC (replacing '447786595432' with the number the user has put in) Jason --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.590 / Virus Database: 373 - Release Date: 16/02/2004 -- * * Please support the community that supports you. * * http://evolt.org/help_support_evolt/ For unsubscribe and other options, including the Tip Harvester and archives of thelist go to: http://lists.evolt.org Workers of the Web, evolt !