Hi Tim,

> I need to get the LONGEST matching string from a field in a data
> table.
> Credit Cards have "bin numbers" - the first few digits on a credit
> card determine what rules to apply to the card.
>
> However, it's more sophisticated than that.
>
> If you have an entry in the bin table of "4", for example, then all
> cards that start with "4" will have those rules applied to them.
>
> But say you have an entry in the table of "4556", as well as "4".
> Then all cards that start with "4" will have those rules applied to
> them.
> Except if you have cards that start with "45", "455" or "4556" -
> they will have the rules under "4556".
>
> At the moment, (using Delphi), I cycle through the table and cycle
> through the BIN numbers in each table, using the following code :
> (...)
> Anyone know how I could do this in an SP?

I'd cycle through the card numbers and select them in to a
variable, e.g. CardNum (using FOR SELECT ... INTO CardNum DO).

Now, how many significant positions are there AT MOST at the start
of the card number? Iow, what is the max prefix length? I'll call
it MaxPrefixLen here.

For each card number, do something like this:

Len = MaxPrefixLen;
Found = 0;
while ( Len > 0 and Found = 0 ) do
begin
if ( exists ( select Prefix from PrefixTable
where Prefix = substrlen( :CardNum, 1, :Len ) ) )
then
begin
Found = 1;
// code to apply rules, set a flag, or whatever
// ...
// ...
end
Len = Len - 1;
end

if ( Found = 0 ) then
begin
// code to handle "no rule found"
end


If you create a _lexicographical_ index on PrefixTable, things may
speed up!

Also note that with this code, you don't compare each number to each
prefix, you just check the number against a couple (4?) of possible
prefix matches.


But maybe it could be done even more simply, along the lines of

select cardnumber, prefix, ...
from CardNumberTable cn
join PrefixTable p
on cn.cardnumber starting with p.prefix
where strlen( p.prefix ) =
( select max( strlen( p2.prefix ) )
from CardNumberTable cn2
join PrefixTable p2
on cn2.cardnumber starting with p2.prefix
where cn2.cardnumber = cn.cardnumber )

But this could take more time.


BTW:
There may be all kinds of syntax errors in what I typed above. But I'm
sure either you or FB will find them.
Also check the declararions of substr and substrlen to get the params
right. I think they call the first position 1 but it might also be 0.

BTW 2:
It helps if you give your post a meaningful subject line, e.g. "How to
find longest match".


Greetings,
Paul Vinkenoog