Assumes you have external functions STRLEN and SUBSTR defined.
This is not tested, so there may be some syntax errors.

CREATE PROCEDURE GET_MAX_BIN
(CCNUMBER VARCHAR(16))
RETURNS
(BIN VARCHAR(16));
AS
DECLARE VARIABLE C INTEGER;
DECLARE VARIABLE TEST_BIN VARCHAR(16);
BEGIN
C = 0;
BIN = '';
REPEAT
C = C + 1;
TEST_BIN = SUBSTR(CCNUMBER,1,C);
IF (EXISTS (SELECT * FROM BIN_TABLE WHERE BIN_FIELD STARTING WITH
:TEST_BIN)) THEN
BIN = TEST_BIN;
ELSE
EXIT;
UNTIL C > STRLEN(CCNUMBER);
END



Roger Vellacott


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 :

if not dmStitch.tblPrefixList.Active then
dmStitch.tblPrefixList.Open;
dmStitch.tblPrefixList.First;
j := 0;
while not dmStitch.tblPrefixList.EOF do
begin
Temp := dmStitch.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;




Anyone know how I could do this in an SP?

Thanks in advance,

Tim


[Non-text portions of this message have been removed]



Yahoo! Groups Sponsor

ADVERTISEMENT

<http://rd.yahoo.com/M=251812.3170658.4537139.1261774/D=egroupweb/S=17051153
86:HM/A=1693352/R=0/SIG=11tralmvc/*http://www.netflix.com/Default?mqso=60178
293&partid=3170658> click here

<http://us.adserver.yahoo.com/l?M=251812.3170658.4537139.1261774/D=egroupmai
l/S=:HM/A=1693352/rand=263775303>

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]