[thelist] SQL - best match

[Jason Handby]

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