2

I'm trying to get DISTINCT and NOT NULL values but this doesn't seem to work:

SELECT DISTINCT ITEM 
FROM TABLE 
WHERE ITEM IS NOT NULL

This returns DISTINCT values but it also returns NULL VALUES.

sample:

ITEM
a
a
b
b
c
c
NULL

output:

a
b
c
NULL
Shane Wealti
  • 2,252
  • 3
  • 19
  • 33
Pod Mays
  • 2,563
  • 7
  • 31
  • 44
  • 2
    Nothing wrong with your query. I suspect your data. Try this where clause. `WHERE ITEM IS NOT NULL AND ITEM <> 'NULL'` – Mikael Eriksson Jun 02 '11 at 16:20
  • 1
    The answer you accepted can not explain the results you say that you were getting. Is `ITEM` a `varchar(max)` column that was previously `text`? IF so [see this question](http://stackoverflow.com/q/10295771/73226) – Martin Smith Apr 29 '12 at 09:58

3 Answers3

4

Try this. "IS NOT NULL" and "IS NULL" does not work with blank values. You can cover both, if the column allows blank.

SELECT 
   DISTINCT item FROM table 
WHERE 
   ISNULL(item,'') <> ''
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shrini
  • 199
  • 1
  • 11
  • True, but why would this blank values result in NULL or "NULL" output? –  Jun 02 '11 at 16:25
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Jun 02 '11 at 16:30
  • 1
    This where clause *will filter BLANK values* and NULL values. However, it will not filter 'NULL' values. I am confused. –  Jun 02 '11 at 16:32
  • @pst: I'm with you on this. If there are values with the string NULL this won't work: and they must exist if IS NOT NULL fails and we see NULL in the output- So -1 to this answer – gbn Jun 02 '11 at 18:41
  • I will take the criticism as didn't expect the string "NULL" to be physically in the data. Also everyone expects every schema too perfect on how it stores data. If the column truly does not accept null as value (not "NULL" string), then best choice is NULLIF(item,'NULL') IS NOT NULL. – Shrini Jun 02 '11 at 21:34
3

This statement will return a b c unless you have the string NULL not the symbol in the column:

SELECT DISTINCT ITEM FROM TABLE WHERE ITEM IS NOT NULL

To test this out, try these

--check for
SELECT DISTINCT ITEM FROM TABLE WHERE ITEM = 'NULL'

--modify original query
SELECT DISTINCT ITEM FROM TABLE WHERE NULLIF(ITEM, 'NULL') IS NOT NULL
gbn
  • 422,506
  • 82
  • 585
  • 676
1

Check if Item is of type Varchar and you are storing the value "NULL" in it. If so then please try the query given below:

select distinct item from table where ISNULL(item,'')<>'' and item <> "NULL"
Raja
  • 3,608
  • 3
  • 28
  • 39
  • That clause is over-complicated and will also *suppress blank fields*! I think that `ITEM <> 'NULL' AND ITEM IS NOT NULL` may be better suited here. –  Jun 02 '11 at 16:28