如何在SQL中过滤掉包含空数据或空数据的特定列的行？

In SQL, How we make a check to filter all row which contain a column data is null or empty ?
For examile

在SQL中,我们如何检查过滤包含列数据的所有行是null还是空?为了检验

Select Name,Age from MEMBERS

We need a check Name should not equal to null or empty.

我们需要一个检查名称不应该等于null或为空。

6 个解决方案

#1

This will work in all sane databases (wink, wink) and will return the rows for which name is not null nor empty

这将适用于所有理智的数据库(wink,wink),并将返回名称不为null或为空的行

select name,age from members where name is not null and name <> ''

#2

For DBMSs that treat '' as a value (not null), Vinko's query works:

对于将''视为值(非空)的DBMS,Vinko的查询有效:

select name,age from members where name is not null and name <> ''

For Oracle, which treats '' as a null, tha above doesn't work but this does:

对于将''视为null的Oracle,上面的内容不起作用,但这样做:

select name,age from members where name is not null

I tried to think of a "DBMS-agnostic" solution, but failed - mainly due to the different concatenation operators/functions used by different DBMSs!

我试着想到一个“DBMS不可知”的解决方案,但失败了 - 主要是由于不同DBMS使用不同的连接运算符/函数!

#3

Select Name,Age from MEMBERS where name is null or name = ''

从MEMBERS中选择名称,年龄,其中name为null或name =''

you can find and learn anymore from http://www.w3schools.com/sql/default.asp

您可以从http://www.w3schools.com/sql/default.asp找到并学习

#4

nvl(Name, 'some dumb string') this will return Name if Name is not null and different of '' (oracle, don't know for others). It will be equal to 'some dumb string' if null or equal to ''.

nvl(Name,'some dumb string')如果Name不为null且不同于''(oracle,其他人不知道),这将返回Name。如果为null或等于'',它将等于'some dumb string'。

#5

Depending on the Database being used; try Select Name, Age from Members where name IS NOT NULL

取决于所使用的数据库;尝试从名为IS NOT NULL的成员中选择名称,年龄

if you need to filter the otherway Select Name, Age from Members where name IS NULL

如果你需要过滤其他选择名称,年龄从会员名称IS NULL

Certain RDBMS treat enpty string different from null, and you would need to add; Select Name, Age from Members where Name IS NOT NULL OR Name <> ''

某些RDBMS将enpty字符串视为与null不同,您需要添加;从名称不为NULL或名称<>''的成员中选择名称,年龄

#6

T-SQL

select name,age from members where COALESCE(name, '') <> ''

#1