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!

Perhaps Ets_Se_Categorie is not an empty string, but instead whitespace? You could try trimming it -- something like where ltrim(rtrim(Ets_Se_Categorie)) != '' — sgeddes
– sgeddes, Commented Jan 20, 2016 at 14:06
You want rows where the column is not null AND its not '' (i.e. change from OR to get what you want) — Alex K.
– Alex K., Commented Jan 20, 2016 at 14:07
I just noticed, you are using OR instead of AND in your query to attempted. — Shantanu Gupta
– Shantanu Gupta, Commented Jan 20, 2016 at 14:09

Tim Schmelter · Accepted Answer · 2016-01-20 14:47:36Z

3

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).

edited Jan 20, 2016 at 14:47

answered Jan 20, 2016 at 14:09

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Tim Schmelter Over a year ago

@AndyK: because both conditions must be true to return this record. All records are either NOT NULL or != ''. Consider you have a NULL record, this record is != '', so it is returned. Consider a record with an empty string, this record is NOT NULL, so it is returned. So the OR is causing your issue.

Andy K Over a year ago

fair enough. However, if the record is != '' , should it not return the record too?

Tim Schmelter Over a year ago

Yes, but not if it is NULL. You always need both conditions to be true. So use AND

Sean Lange Over a year ago

You could also simplify this to simply A.Ets_Se_Categorie > '' It will only return values that are not null and have a value other than empty string.

Andy K Over a year ago

I read your comment and it starts to sink in. One last question though: If there was a NULL or an '' , why the count did not see it and put it as a NULL? TIA @tim-schmelter

|

Tom Chantler · Accepted Answer · 2016-01-20 14:20:56Z

2

You can just use ISNULL instead. Like this:

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

answered Jan 20, 2016 at 14:20

Tom Chantler

15k4 gold badges50 silver badges53 bronze badges

3 Comments

Andy K Over a year ago

very elegant! I love it @tom-chantler

Tim Schmelter Over a year ago

or with ANSI SQL COALESCE(A.Ets_Se_Categorie, '') != ''

Tom Chantler Over a year ago

Yep, that's true Tim. Whilst the question is tagged as Microsoft SQL Server specific, you're right that COALESCE is the ANSI answer.

Shantanu Gupta · Accepted Answer · 2016-01-20 14:07:43Z

1

Did you try LTRIM, RTRIM to remove spaces

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

answered Jan 20, 2016 at 14:07

Shantanu Gupta

21.2k56 gold badges187 silver badges294 bronze badges

1 Comment

t-clausen.dk Over a year ago

All this does is making the query slower LTRIM(RTRIM(Ets_Se_Categorie)) != '' is exactly the same as LTRIM(Ets_Se_Categorie) != '' which is even exactly the same as Ets_Se_Categorie != ''. Example comparing no space with space returns 0 rows: SELECT 1 WHERE '' != ' '

fabulaspb · Accepted Answer · 2016-01-20 14:08:06Z

1

Try to check spaces in your column.

SELECT Ets_Se_Categorie AS Ets_Se_Categorie,COUNT(Ets_Se_Categorie) AS cpte
FROM TableV_MyTest
WHERE LEN(ISNULL(Ets_Se_Categorie, '')) <> 0
GROUP BY Ets_Se_Categorie

answered Jan 20, 2016 at 14:08

fabulaspb

1,2538 silver badges9 bronze badges

Comments

db_brad · Accepted Answer · 2016-01-20 14:10:39Z

1

You need to use AND here. It is still returning because it looks and sees that the column is not NULL and returns it. That's how an OR works, it returns if the first clause is true.

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

answered Jan 20, 2016 at 14:10

db_brad

9336 silver badges22 bronze badges

Collectives™ on Stack Overflow

Sql(Server) NULL or empty string still here

5 Answers 5

12 Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

12 Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related