Hi Roger,

>>> SELECT MAX(bin_num) FROM bin_table WHERE :search_bin STARTING
>>> WITH bin_num
>>
>> This looks like the simplest and best solution so far!

> But be careful. This will only work if....
>
> No numbers begin with 0
> All strings involved are numeric
>
> In the case of credit card numbers, this is probably a reasonable
> assumption..
>
> You are also assuming that Firebird will cast bin_num as a number
> for "Max(bin_num)" and as a string for "Starting with bin_num".
> Otherwise 99 could well be seen as greater than 1000. Is this a
> reliable assumption?

Max also works with strings, in which case it will always return the
lexicographically 'highest' match. Of course Max must only consider
strings that _do_ match. In this case, it doesn't even matter whether
bin_num is string or numeric. Among the matching prefixes, the numeric
maximum equals the lex. maximum:

'4332' > '433' > '43' > '4'
'0078' > '007' > '00' > '0'

Come to think of it: if prefixes can start with one or more zeroes,
you must make sure that Max gets a string here, not a number!
Otherwise it could return 0 or 8 as the maximum, where it should be
000 or 0008 !

But, thinking a little further... if bin_num is numeric, no whole
number other than 0 will start with 0. So even then there would be
no problem.


Grtz,
Paul