[thelist] SQL - best match

Maximillian Schwanekamp anaxamaxan at neptunewebworks.com
Tue Feb 17 04:51:40 CST 2004


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 !






More information about the thelist mailing list