Selecting not null values

Question

I have 5 columns and I'm trying to select all with out null values.  Looking below how can I get all 3 records without the empty fileds?

 

id                       column1             column2            column3          column4             column5

123                       test                   test                    test               test

456                       test                   test

789                       test

 

 

select column1,column2,column3,column4,column5

from Table

Where column1<> '' or column2<> '' column3<>'' or column4 <>'' or column5<>''

 

I've tried a union all and joins but can't get all records when it gets to a column that is null.

Answer 1

Code Snippet

select column1,column2,column3,column4,column5

from Table

Where isnull(column1,'')<> '' or isnull(column2,'')<> '' ......

 

 

 

Is this your meaning?

Answer 2

To check if a value is NULL or not, you should use " column IS NOT NULL " or " column IS NULL ".

 

What is the out put you are expecting? Are you looking for rows where atleast one column is NOT NULL? or are you looking for columns where all the 5 columns are NOT NULL?

Answer 3

I don't want anything if the column is null.  I just want non null columns for each record.

Answer 4

mr4100 wrote:

I don't want anything if the column is null.  I just want non null columns for each record.

 

So,what 's your result by your example data.

Answer 5

well, after thinking about I don't think it's possible with what I'm trying to do.  I'm using a dataflow task in ssis and what I want to do is use one source to insert only non null records into the db.  I think I have to have a separate souce to not pick up the null values but I thought I could nest the queries together into one soure and use a multicast task to split the data up by the column names.  This probably doesn't make sense but I think I need to have 5 separate dataflow tasks and just make the same query for each and for each column set my where clause to <> '' I DON'T KNOW AND TGIF!!!

 

Answer 6

 

Code Snippet

coalesce(column1,column2,column3,column4,column5) is not null;

 

 

Below one is very similar logic .

 

Code Snippet

Least(column1,column2,column3,column4,column5) is not null;

 

 

Answer 7

this doesn't help either but I figured out a way to do it.  I just use more than one source for each task.  It seems to work fine as long as I have the nolock option unchecked in my destination.  Thanks for the help.