[thelist] SQL - best match
Jason Handby
jason_handby at illustraresearch.com
Tue Feb 17 04:17:04 CST 2004
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
More information about the thelist
mailing list