Assuming your table looks something like this:
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
id column1 column2 column3 ... 187 2 9 null 456 null 37 42 937 7 12 4 ...
The real answer is: you shouldn't need to. The answer to the question you asked is:
select case when column1 is null then 0 else 1 end +case when column2 is null then 0 else 1 end +case when column3 is null then 0 else 1 end +... as not_null_columns from TableA where id = 937
Any time you find yourself wanting to do something to "all columns in a row" like this, step back and consider redesigning the table. There's a good chance the table isn't in first normal form. The main symptom is finding yourself writing clumsy queries with a lot of repetitive expressions.
A normalized one-to-many table would look something like this:
id columnx columnvalue 187 1 2 187 2 9 456 2 37 456 3 42 937 1 7 937 2 12 937 3 4 ...
Now the query gets a lot simpler:
select count(columnx) as not_null_columns from TableB where id = 937
Much nicer, wouldn't you agree?
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.