Sql(Server) NULL or empty string still here

Question

I have this query

SELECT Ets_Se_Categorie AS Ets_Se_Categorie,COUNT(Ets_Se_Categorie) AS cpte
FROM TableV_MyTest
GROUP BY Ets_Se_Categorie

It gives me the following table / results

Ets_Se_Categorie                                            cpte
(Seems to be empty string)                                  5531
Old place                                                   8
Secondary                                                   1066
Principal                                                   4713
Subsidiary                                                  7985
First                                                       9
headquarter                                                 31610
Main Headquarter                                            1587

The (Seems to be empty string) means it is an empty field

Aim is to have this

Ets_Se_Categorie                                            cpte
Old place                                                   8
Secondary                                                   1066
Principal                                                   4713
Subsidiary                                                  7985
First                                                       9
headquarter                                                 31610
Main Headquarter                                            1587

I've created this query

 SELECT *
 FROM
 (
 SELECT Ets_Se_Categorie AS Ets_Se_Categorie,COUNT(Ets_Se_Categorie) AS cpte
 FROM TableV_MyTest 
 GROUP BY Ets_Se_Categorie
 ) AS A
 WHERE (A.Ets_Se_Categorie IS NOT NULL OR A.Ets_Se_Categorie != '')

Issue is nothing has changed ...

Why did I use a nested query?

I tried this, first

 SELECT Ets_Se_Categorie,COUNT(Ets_Se_Categorie) AS cpte
 FROM TableV_MyTest 
 WHERE (Ets_Se_Categorie IS NOT NULL OR Ets_Se_Categorie != '')
 GROUP BY Ets_Se_Categorie

Nothing happened...

Any insights would be greatly appreciated. Probably very simple but I'm bit confused on the why.

Thanks!

Answer 1

You have used OR instead of AND, this should work:

WHERE (A.Ets_Se_Categorie IS NOT NULL AND A.Ets_Se_Categorie != '')

Since there was confusion about why the OR didn't work as expected:

Both conditions must be true to return a record. All records are either NOT NULL or != ''. Consider a record with an empty string, this record is NOT NULL, so it is returned.

Why you didn't see the NULL records:

NULL is not a valid value, it means undefined. So nothing is either equal or unequal to NULL. Neither NULL = NULL nor Anything != NULL is true. You have to use IS (NOT) NULL.

In your original query you've filtered out the NULL values:

 SELECT Ets_Se_Categorie,COUNT(Ets_Se_Categorie) AS cpte
 FROM TableV_MyTest 
 WHERE (Ets_Se_Categorie IS NOT NULL OR Ets_Se_Categorie != '')
 GROUP BY Ets_Se_Categorie

because a NULL would neither be NOT NULL nor != ''(remember last section).

Conclusion: either use IS NOT NULL AND != '' or ISNULL(Column, '') != '' or COALESCE(Column, '') != '' which is ANSI sql (works in all databases).

Answer 2

You can just use ISNULL instead. Like this:

WHERE ISNULL(A.Ets_Se_Categorie, '') != ''