检查约束以确保字符列中的值都是数字 [英] Check constraint to make sure values in a character column are all digits

问题描述

这类似于 CHECK CONSTRAINT of string to只包含数字.(Oracle SQL) 但我想为 Sybase 做这个.

This is similar to CHECK CONSTRAINT of string to contain only digits. (Oracle SQL) but I want to do this for Sybase.

我有一个字符列colExp"(8 个字符).我想设置一个检查约束以确保此列的值都是数字.

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]'

推荐答案

它会伤害大脑，而不是 NOT LIKE (not range) 工作.

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]%'

双负并处理任意长度

这篇关于检查约束以确保字符列中的值都是数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！