How filter out the rows which contain a particular column with null or empty data in SQL ?

Question

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

Select Name,Age from MEMBERS

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

Answer 1

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

Answer 2

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

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

Answer 3

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

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

Answer 4

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

if you need to filter the otherway Select Name, Age from Members where name 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 <> ''

Answer 5

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

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:

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!

Answer 6

T-SQL

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