[thelist] SQL - best match

[Jason Handby]

> Dear all
> 
> I have a table in a SQLServer 2k database that contains a 
> column containing telephone codes. The telephone codes may be 
> just the country code (eg. 44), the country code and that of 
> London (eg. 4420), the country code for a region of London 
> (eg. 44207), the code for a mobile (eg. 4407), the code for a 
> specific mobile network (eg. 44778) etc. A user will input 
> their phone number through a form on a web page, and I need 
> to query the database to get the best match for that number; 
> ie if there is a record for the code 441745 I want to return 
> this record, not the generic code 44. I have already done it 
> using a loop in ASP, but this seems clumsy and inefficient. 
> Can anyone think of a way to cleanly return the best match 
> using a SQL statement?

You can get all the codes beginning '44' like this:

	SELECT * FROM MyTable WHERE TelephoneCode LIKE '44%'

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


Hope this helps!




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