Numeric value only for a character field

[David Greenberg]

Hi
I have a character field (char ot varchar) that I want to force only to
contain numeric characters.
Can that be done by way of defining a constraint on the field ?
or by any other way in the field/table definition ?
What id the syntax ?
Anyone have examples ?

Thanks

David Greenberg

[Hugo Kornelis]

On Thu, 02 Aug 2007 10:05:40 +0200, David Greenberg wrote:

>Hi
I have a character field (char ot varchar) that I want to force only to
contain numeric characters.
Can that be done by way of defining a constraint on the field ?
or by any other way in the field/table definition ?
What id the syntax ?
Anyone have examples ?

Hi David,

The obvious solution is to declare the column with one of the numeric
data types instead of char or varchar.

But if you insist on using character columns for numeric data, you can
add a CHECK constraint:

CREATE TABLE Example
(SomeCol char(9) CHECK (SomeCol NOT LIKE '%[^0-9]%'));

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis