Check constraint to make sure values in a character column are all digits.

Question

This is similar to http://stackoverflow.com/questions/1167767/check-constraint-of-string-to-contain-only-digits-oracle-sql but I want to do this for Sybase.

I have a character column 'colExp' (8 characters). I want to put a check constraint to make sure values for this column are all digits.

How can I do this? This will work but its not elegant

colExp LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Answer 1

It hurts the brain, not NOT LIKE (not range) works.

SELECT 1 WHERE 'foo' NOT LIKE '%[^0-9]%'

SELECT 1 WHERE '123' NOT LIKE '%[^0-9]%'

SELECT 1 WHERE 'aa1' NOT LIKE '%[^0-9]%'

SELECT 1 WHERE '1bb' NOT LIKE '%[^0-9]%'

SELECT 1 WHERE NULL NOT LIKE '%[^0-9]%'

SELECT 1 WHERE '   ' NOT LIKE '%[^0-9]%'

--FAIL. Need extra check. Perhaps NULLIF
SELECT 1 WHERE '' NOT LIKE '%[^0-9]%'

Double negative and deals with any length