Gudradain
Gudradain

Reputation: 4753

SQL Server - Select column where at least one value is not null

I want to select all columns where at least one value is not null. For example :

TableUser
----------------------------
Name   |  Email  |  Phone
----------------------------
User1  |  NULL   |  123
User2  |  NULL   |  NULL
User3  |  NULL   |  NULL

Should produce

------------------
Name   |  Phone
------------------
User1  |  123
User2  |  NULL
User3  |  NULL

Is it possible to create a function to do it for you given a result set?

For example :

removeNull(SELECT * FROM TableUser WHERE Phone IS NOT NULL);

Upvotes: 1

Views: 3003

Answers (1)

Tanner_Gram
Tanner_Gram

Reputation: 1110

How to detect whether a given column has only the NULL value:

 SELECT 1 
      FROM tableuser
    HAVING COUNT(email) = 0 
           AND COUNT(*) > 0

The resultset will either consist of zero rows (column a has a non-NULL value) or one row (column a has only the NULL value).

Upvotes: 1

Related Questions