Subject Re: [firebird-support] Re: CHECK for all numeric characters
Author Ivan Prenosil
> Here's the final version, in case anyone is interested.
>
> CAST ('1' || VALUE AS NUMERIC(11,0)) BETWEEN 100000000000 AND
> 199999999999
> AND VALUE NOT CONTAINING 'e'
> AND VALUE NOT CONTAINING 'E'
> AND VALUE NOT CONTAINING ','
> AND VALUE NOT CONTAINING '.'

It is unnecessarily long. This test
CAST ('1' || Value AS BIGINT) BETWEEN 100000000000 AND 199999999999
will catch nearly everything, i.e. if the string contains any of legal characters
like ' ', '.', '+', '-', then the number of digits will be less than 11, and the BETWEEN will fail.
The only case left are numbers with exponent, like
11e10
and this is taken care of by CONTAINING, which is case-insensitive, so you do not
have to use it twice.
There is also no difference between BIGINT and NUMERIC(11,0) because
both are simple Int64.

This should be enough:
CHECK ( CAST ('1' || Value AS BIGINT) BETWEEN 100000000000 AND 199999999999
AND Value NOT CONTAINING 'e')


Ivan


>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Ivan Prenosil
> Sent: Friday, September 02, 2005 11:23 AM
> To: Firebird-Support
> Subject: Re: [firebird-support] Re: CHECK for all numeric characters
>
>> You are also correct. Perhaps I should cast it as NUMERIC(11,0).
>
> Now I see the solution I sent yesterday arrived distorted somehow (I
> have no idea how it could happen, since I copy/pasted it from isql), so
> I will try again:
>
> CHECK ( CAST ('1' || Value AS BIGINT) BETWEEN 100000000000 AND
> 199999999999
> AND Value NOT CONTAINING 'e')
>
> Ivan