Subject | RE: [firebird-support] Help |
---|---|

Author | Roger Vellacott |

Post date | 2003-08-06T09:50:09Z |

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]

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]