"David Johnson" wrote:

> On Thu, 2005-09-01 at 18:26 +0200, Ivan Prenosil wrote:
>> > I'm creating a domain that's CHAR(11) and I'd like to add a check to
>> > make sure all the characters are numeric. I'm experiencing a brain
>> > freeze right now and can't come up with one. Can anyone suggest the
>> > appropriate CHECK statement(s)?
>>
>> CHECK (Value BETWEEN '00000000000' AND '99999999999')
>>
>> Ivan
>>
>>
>
> Not quite ... this will allow '0A'.

oops, sorry.

>
> I have recently had to coerce some character columns with garbage data
> into numerics in another SQL dialect. I believe that there are UDF's
> that will do this task:
>
> length(rtrim(transform(column, '0123456789', ' '))) = 0

Long/ugly, but without UDF:

CHECK ( SUBSTRING(Value FROM 1 FOR 1) BETWEEN '0' AND '9' AND
SUBSTRING(Value FROM 2 FOR 1) BETWEEN '0' AND '9' AND
SUBSTRING(Value FROM 3 FOR 1) BETWEEN '0' AND '9' AND
SUBSTRING(Value FROM 4 FOR 1) BETWEEN '0' AND '9' AND ...


And what about this:

CHECK ( CAST ('1' || Value) BETWEEN 100000000000 199999999999 )


Ivan