SQL Query to exclude the null

Dear expert,

Simple query, I want MSSQL to exclude all the NULL result in columns from the table.

Any easy solution?
Thx
LVL 1
WeTiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MishaProgrammerCommented:
select * from YourTableName WHERE NOT YourColumn IS NULL

Open in new window

0
Ryan ChongCommented:
I want MSSQL to exclude all the NULL result in columns from the table.
how you want to do the comparison? exclude them and ... replace with some values? or exclude the rows if any column got NULL value?

pls provide an example.
0
WeTiAuthor Commented:
WHERE NOT YourColumn this YourColumn, you need to spec all the columns, ok I know this already, but I want to show all columns, this table got around 60s columns, some of them are null some not. How do i exclude all the columns that is null?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
How do i exclude all the columns that is null?

do you want to exclude the record (row) or columns??
0
WeTiAuthor Commented:
Exclude the columns when the results is null, this only apply for result that showing one row, if more rows then it wouldn't work I know.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Exclude the columns when the results is null, this only apply for result that showing one row, if more rows then it wouldn't work I know.

It would be great and easier if you could post the query and explain which columns you don't want NULL values to be displayed..
If you want the difficult approach, then follow the steps below..
1. Add only NOT NULL columns in your SELECT clause.
2. Don't do LEFT JOIN in your SELECT query as it would bring NULL values in few columns referenced from that table.
3. Don't use Subqueries as it might bring NULL values.
4. Don't use Scalar or Table valued functions as it might bring NULL Values.

>> I want to show all columns, this table got around 60s columns, some of them are null some not. How do i exclude all the columns that is null?

Then you might need to add the condition for all NULL able columns in the WHERE condition like this
WHERE nullable_col1 IS NOT NULL and nullable_col2 IS NOT NULL 

Open in new window

0
WeTiAuthor Commented:
Here is the exemple:

Name | Address | Log | info | number | system
WeTi   | null       | 2     | 2      | null         | null

Now i want to exclude the address and number and system columns, this is a easy result, my result got 50+ columns more, Im only interested in those columns that have information in it.  How do i do?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Now i want to exclude the address and number and system columns

Okay, if that is the case, then you can follow my first comment mentioned.. Seems address, number and system columns are Nullable and hence remove all those Nullable columns in your WHERE clause..
1. Add only NOT NULL columns in your SELECT clause.
0
WeTiAuthor Commented:
You mean this?
WHERE nullable_col1 IS NOT NULL and nullable_col2 IS NOT NULL
this means I need to add 20+ columns here...
0
slightwv (䄆 Netminder) Commented:
Do you physically want to remove the columns from the result set?

Using your example above you want the columns returned as:
Name | Log | info
WeTi   |  2     | 2

Or do you not want the ROW returned?

What if you have data like:
Name | Address | Log | info | number | system
WeTi   | null       | 2     | 2      | null         | null
null   | 2       | null     | 2      | null         | null


Dynamic columns in a result set is quite messy.  If you can provide the requirements behind this request, we might be able to offer alternative solutions.
0
awking00Commented:
>>Here is the exemple:
 Name | Address | Log | info | number | system
 WeTi   | null       | 2     | 2      | null         | null
<<

I think what you are asking may not be possible.
So, in your example you would only want to return Name, Log, and info?
What if there is another record like -
 WeTi   | 123 Main St.       |null     | null      | 5         | null
How would you only return Name, Address, and number for this record while returning different columns for the first record?

The only possibility I see is that you could select columns omitting the ones where EVERY value is null. This would have to be done dynamically to test which columns would qualify (or not), but it could be done. Perhaps you could provide some sample data (maybe pick 10 or so columns) and what you would expect as the output from that data. It would help in testing any solutions.
0
WeTiAuthor Commented:
Yes as Awking00 says it's not possible if that is the case, but in my case I only get one row in result and want to remove all the null in all the columns of the result if there is one.
0
slightwv (䄆 Netminder) Commented:
Please confirm what I asked above:

Using your example above you want the columns returned as:
Name | Log | info
WeTi   |  2     | 2


So, 1 row with three columns.

The number of columns will be dynamic.
0
Mark WillsTopic AdvisorCommented:
Indeed the number of columns will be dynamic, such that from row to row it will be indistinguishable, unless of course, some kind of metadata / tags (like XML) to identify which column is which....

But WeTi has also said he only wants one row....

Will watch with great interest and think I can help.

Will probably need to be a stored procedure - with parameter to identify the unique row.

Maybe an example (filling in the <?> with meaningful names) :  select <columns> from <table name> where <conditions>

Adding a bit more to :
 Name | Address | Log | info | number | system
 WeTi   | null       | 2     | 2      | null         | null

Is there some DDL / table definition ?

Will await greater clarification from WeTi
0
WeTiAuthor Commented:
Name | Address | Log | info | number | system
 WeTi   | null       | 2     | 2      | null         | null

This is only a exemple of how result select query table looks like in database. For me to know those null columns is not interesting at all for me to know, and I need to scroll loooong to the left or right to find more info that interest me. This is why I would like to know how to do it... I think why and why was irrelevant here anyway, now I only want to know the:
Name | Log | info
WeTi   |  2     | 2
Address, number, system exclude. now I can as i know select only those columns in the select name, log, info, to exclude those info yes however we got like 10000 tables and all of them looks diffirent from each other... this is a headache for me to check which columns are there and exclude them.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.