CODEWITHSUNDEEP
sql-server-2008nullgroup-bystring
Geethu
Geethu

Reputation: 71

Group null and empty string together

I want to group the NULL and empty fields in a column together .

I tried the below script

SELECT
  CASE
    WHEN test IS NULL OR test= ''
    THEN ''
    ELSE test
  END as 'test' 
from firm 
group by test

If we are having the values NULL,'',Test1,Test2 in the column test,the above query will create two groups with NULL values as below.

1 NULL

2 NULL

3 Test1

4 Test2

What I want is a grouping shown below.

1 NULL or ''

2 Test1

3 Test2

Upvotes: 5

Views: 5623

Answers (3)

Al-3sli
Al-3sli

Reputation: 2181

try this way :

Select Case when IsNull(test,'') = '' Then '' Else test End as test 
From Firm
Group By IsNull(test,'')

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

Your CASE is reinventing COALESCE:

SELECT
  COALESCE(test, '') as test
from firm 
group by COALESCE(test, '')

or simply:

SELECT DISTINCT
  COALESCE(test, '') as test
from firm

Upvotes: 8

Jade
Jade

Reputation: 2992

Try this hope this helps you

SELECT
  CASE
    WHEN test IS NULL OR test= ''
    THEN ''
    ELSE test
  END as 'test' 
from firm 
group by CASE WHEN test IS NULL OR test = '' THEN '' ELSE test END

OR

SELECT
  CASE
    WHEN test IS NULL OR test= ''
    THEN ''
    ELSE test
  END as 'test' 
from firm 
group by isnull(test, '')

Upvotes: 9

Related Questions