[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