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

Author | Paul Vinkenoog |

Post date | 2003-08-06T14:13:01Z |

Hi Roger,

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

>>> SELECT MAX(bin_num) FROM bin_table WHERE :search_bin STARTINGMax also works with strings, in which case it will always return the

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

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