SQL Server 2012 LIKE expression pattern

Question

I am working on an assignment that requires that I add a constraint that makes sure that all values in a column are purely numerical. Im starting with

ALTER TABLE table_name
ADD CONSTRAINT ck_name CHECK (col_name LIKE 'pattern');

The problem is that the values can vary in length. I've been doing a lot of reading but i cant figure out a pattern that will limit it to numbers but still let it vary in length. I know in MySQL you can do '[0-9]*', but this doesn't work on SQL Server. Can anyone point me in the direction of an equivalent or confirm that it cant happen/i am approaching it wrong?

Edit: I have to use a constraint, per the assignment requirements.

If you want that column was numerical just make it of numerical type. — Hamlet Hakobyan, Apr 23 '14 at 22:07
http://stackoverflow.com/questions/1736630/sql-constraint-minvalue-maxvalue — Robert Harvey, Apr 23 '14 at 22:10
Hamlet, I have to use a constraint, per the assignment requirements. — southerneagle16, Apr 23 '14 at 22:54

score 0 · Answer 1 · edited Apr 23 '14 at 23:36

0

   ADD CONSTRAINT ck_name CHECK (TRY_PARSE(col_name AS int) IS NOT NULL);

Haven't tried this but might work?

edited Apr 23 '14 at 23:36

Rahul

76,197
13
71
125

answered Apr 23 '14 at 22:15

sarin

5,227
3
34
63

This defeats the point of the assignment. It has to be a CHECK constraint. Thanks though! – southerneagle16 Apr 23 '14 at 22:52
@southerneagle16 I meant to add it in the check constraint. see answer again. TRY_PARSE could also be useful. It just has to be a valid expression I think. i.e. returns true\false – sarin Apr 23 '14 at 23:18

score 0 · Answer 2 · answered Apr 23 '14 at 23:31

0

You can do something like this

ALTER TABLE table_name
ADD CONSTRAINT ck_name CHECK (ISNUMERIC(col_name)<>0);

(OR) something like this

ALTER TABLE table_name
ADD CONSTRAINT ck_name 
CHECK (not col_name like '%[^0-9]%');

answered Apr 23 '14 at 23:31

Rahul

76,197
13
71
125

SQL Server 2012 LIKE expression pattern

2 Answers2