CODEWITHSUNDEEP
sql
Nakul Chaudhary
Nakul Chaudhary

Reputation: 26144

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

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.

Upvotes: 11

Views: 44945

Answers (6)

Fuangwith S.
Fuangwith S.

Reputation: 5764

SELECT Name,Age FROM MEMBERS WHERE name IS NOT null OR name <> ''

You can get more informations from http://www.w3schools.com/sql/default.asp

Upvotes: 1

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340171

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

Upvotes: 19

edosoft
edosoft

Reputation: 17271

T-SQL

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

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

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!

Upvotes: 5

Dheer
Dheer

Reputation: 4066

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

Upvotes: 0

Gilles
Gilles

Reputation: 409

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

Upvotes: 0

Related Questions