CODEWITHSUNDEEP
sql-server
Tony
Tony

Reputation: 3409

Select Where clause

I am doing a Select in SQL Server and say my Select is pulling the data like this,

ID Col1 Col2 Col3 Col4
1   xx   xx   xx   xx
2   null null null null
3   xx   xx   null null

I only want the records where not all the rows are Null. In above record, I don't want the row where ID= 2.

How can I do this in where clause?

Thanks.

Upvotes: 1

Views: 135

Answers (4)

DForck42
DForck42

Reputation: 20387

Select Col1, Col2, Col3, Col4
from table
where Col1>''
and Col2>''
and Col3>''
and Col4>''

What this does is it selects all rows that actually have data in them. However, if you have char fields that are balnk but not null that you need to select, you'll have to change this to >=. This query will also utalize a covering index i believe.

Upvotes: 0

Vinodharajan
Vinodharajan

Reputation: 79

Where not ( Col1 Is Null
    And Col2 Is Null
    And Col3 Is Null
    And Col4 Is Null)

This will make sure that all the columns are not null

Upvotes: 1

Thomas
Thomas

Reputation: 64674

An alternate to Martin's solution:

Where Col1 Is Not Null
    Or Col2 Is Not Null
    Or Col3 Is Not Null
    Or Col4 Is Not Null

It should be noted that if any of the columns are not implicitly castable to the same datatype (e.g. all varchar or all ints), that COALESCE will throw an error.

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453837

Do they all have the same datatype? If so

WHERE COALESCE(Col1, Col2, Col3, Col4) IS NOT NULL

Upvotes: 5

Related Questions