Hi everyone,

I have an application written in Delphi 5 using dBase files that handles
EFT transactions for Credit Cards.

I am converting the application to use Firebird.

During part of the process, the application must find the longest matching
card prefix for the card being used.

So, for example, the following prefixes start with "9" :

903510
91
913086
92
925703
934910
934911
94
944042
947

Each different number represents a card with different business rules that
must be applied to the card.

The Card Number can be up to 32 characters long. In order to find the
appropriate rules for the credit card, I need to find the longest matching
prefix that matches the card number.

So, for example, the longest prefix matching a card that starts with the
numbers '92310' will be the prefix '92'. The longest prefix matching a card
that starts with the numbers '94413' will be the prefix '944042'.

The prefix numbers are ordered and matched by the string number itself. So
the longest prefix matching a card starting with '99902' will be the first
prefix in the list - '903510'.

I hope I am explaining the situation well enough.

Now for the question :

Is there any way I could write a stored procedure to do this?

At the moment, I do it in the application itself. I would prefer the
database to do this for me.

Here is the Delphi code :

lFoundPrefix := false;
if not tblPrefixList.Active then
tblPrefixList.Open;
tblPrefixList.First;
j := 0;
while not tblPrefixList.EOF do
begin
Temp := tblPrefixListPREFIX.AsString;
if Copy(APAN,1,Length(Temp)) = Copy(Temp,1,Length(Temp)) then
begin
i := Length(Temp);
if (i > j) then
begin
j := i;
LongestBIN := Temp;
end;
lFoundPrefix := true;
//Break;
end;
dmStitch.tblPrefixList.Next;
end;
finally
if tblPrefixList.Active then
tblPrefixList.Close;
end;
if lFoundPrefix = true then
begin
TheRecord := GetBinInfo(LongestBIN, APAN);
end else
begin
TheRecord.Prefix := ''; { This will indicate that the prefix was
not found }
end;
ThePrefixRecord := TheRecord;

Thanks in advance,

Tim