By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,396 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Check null values for more than one column

rsrinivasan
100+
P: 221
Hi all,

I have one table with 24 fields in SQL server.
In this some of the fields has NULL value.
But i want to get the records which does not have any NULL value in any one of the fields.
This is my Query:
Expand|Select|Wrap|Line Numbers
  1. select * from tablename
  2. where  v1  is not null and v2  is not null and  
  3. v3  is not null and v4  is not null and  
  4. v5  is not null and v6  is not null and
  5. v7  is not null and v8  is not null and
  6. v9  is not null and v10 is not null and
  7. v11 is not null and v12 is not null and
  8. v13 is not null and v14 is not null and
  9. v15 is not null and v16 is not null and
  10. v17 is not null and v18 is not null and
  11. v19 is not null and v20 is not null and
  12. v21 is not null and v22 is not null and
  13. v23 is not null and v24 is not null
Is it correct? Or can i use some other method. Because i have millions of records in this table.
Jun 6 '08 #1
Share this Question
Share on Google+
4 Replies


zachster17
P: 30
I think that's the only way; below is a variation I actually to check records with no null values using ISNULL.

SELECT * FROM tablename
WHERE ISNULL(v1 + v2 + v3 + v4 + v5 + v6 + ... + v24,'n') <> 'n'

If anything inside is null it treats the whole thing as null.

where n are the fields you want to check for null values
All the fields I check are varchar fields, but I think you would have to use cast or ocnvert for nonvarchar fields (if you use 'n' as the null value)

Thanks,

Zach
Jun 8 '08 #2

ck9663
Expert 2.5K+
P: 2,878
That would depend on the setting of SET CONCAT_NULL_YIELDS_NULL. For more details read this

-- CK
Jun 9 '08 #3

10K+
P: 13,264
If you are using some front-end language you could shorten the code by creating that query using a loop.
Jun 9 '08 #4

P: 1
i have the same issue
would u please explained to me the deatil expression of isNull function in oracle or if any one has other way to create query to check null values for more than one column where the both of the column has number value.
i faced error when i write the where statement as follow:
select column1, column2
from table
where coulmn1 and coulmn2 is not null;
May 9 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.