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

Author | Paul Vinkenoog |

Post date | 2003-08-06T10:33:32Z |

Hi Tim,

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

> I need to get the LONGEST matching string from a field in a dataI'd cycle through the card numbers and select them in to a

> 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?

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