CODEWITHSUNDEEP
sqlsybase
chikak
chikak

Reputation: 1732

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

This is similar to 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]'

Upvotes: 0

Views: 1962

Answers (1)

gbn
gbn

Reputation: 432261

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

Upvotes: 2

Related Questions