开发者

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开发者_Go百科 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.


Do they all have the same datatype? If so

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


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.


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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜