Got it, thanks.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ivan Prenosil
Sent: Tuesday, September 06, 2005 11:23 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: CHECK for all numeric characters

> 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

------------------------ Yahoo! Groups Sponsor --------------------~-->
Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet
Life.
http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links