Geek Answers Handbook

Is there a better way to find rows with an entire column with values?

I need to find rows in result sets, each of which contains zero.

These result sets have a variable number of columns.

Currently, the only way I can think of is to create a view for each result set and filter this way:

select field1, field2, field3, field4, ... from "huge query" where field1 is not null and field2 is not null and field3 is not null and field4 is not null and ... is not null

is there a better way to do this in a stored procedure / function in SQL Server or in .net code (C # or vb.net)?

What about doing something like

  select field1, field2, field3, field4, ... from "huge query" (return to .net apps or insert into #temptable)

and then in a stored procedure / function or .net code (C # / vb.net) loop through all rows / columns and a flag or delete every row that received zero?

I am talking about more than 50 different types of result sets and it will grow over time, so I am looking for a general / easily maintained way.

+4
source share
3 answers

Since nulls propagate if they are all the same data types, try

 where colA + ColB + ColC, etc Is Not Null

If this is not the case, first convert them (those that are not yet a string) to char, and then concatenate them.

 where Str(ColA) + Str(ColB) + Str(ColC), etc Is Not Null
+3
source

Your method is not very good, but I really think that it will work best. Another way is to do

 WHERE (field1 + field2 + field3 + field4) IS NOT NULL
+3
source

If field1, field2, field2 ... fieldX are strings, you can try:

 select field1, field2, field3, field4, ... from "huge query" where field1 + field2 + field3 + ... + fieldX is not null
0
source

More articles:


All Articles