在SQL Server中，如何检查列值是否只包含零?

Question

假设:Sample为数据库，Column_Name为表。

示例输入:

Column_Name
12345
000
00
00000

输出:

Column_Name
000
00
00000
Select * from Sample.Column_Name where  Column_Name like '[0]+'

上面的代码是有效的正确的方法是什么?

Answer 1

确切的解决方案应该是:

SELECT * 
FROM   Sample.Column_Name 
WHERE  Column_Name = REPLICATE('0', LEN(Column_Name ));

假设Column_Name是一个VARCHAR数据类型列

Answer 2

您可以像这样使用通配符not like:

where column_name not like '%[^0]%'

要确定有一个零:

where column_name not like '%[^0]%' and column_name like '%0%'

您也可以使用replace():

where replace(column_name, '0', '') = ''

或甚至try_convert():

where try_convert(int, column_name) = 0

但是，也可以接受-0、+0和空格。

Answer 3

select * from Sample.Column_Name where isnumeric(Column_Name) = 1 and cast(Column_Name as int) == 0