Displaying NULLS but not Blanks

Hi,
I am using the followng query:
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
ORDER BY BRAND ASC
In all the data displayed by this query, there is also a Null value and a
Blank value. Is there any way, I can display the null value but not the blan
k
value?
--
pmudTry:
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
AND BRAND <> ''
ORDER BY BRAND ASC
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:AC2C98C0-9DCD-430C-BA97-A50C60FEEE6C@.microsoft.com...
Hi,
I am using the followng query:
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
ORDER BY BRAND ASC
In all the data displayed by this query, there is also a Null value and a
Blank value. Is there any way, I can display the null value but not the
blank
value?
--
pmud|||SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T' AND BRAND <> ''
ORDER BY BRAND ASC
"pmud" wrote:

> Hi,
> I am using the followng query:
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T'
> ORDER BY BRAND ASC
> In all the data displayed by this query, there is also a Null value and a
> Blank value. Is there any way, I can display the null value but not the bl
ank
> value?
> --
> pmud|||Hi,
I have already tried that. It doesnt even display the NULL value. I wabt to
display those values where the data is <NULL> , but not those where the fiel
d
is blank.. i mean where the cell is completely blank.
Any other ideas?
Thanks
--
pmud
"KH" wrote:
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T' AND BRAND <> ''
> ORDER BY BRAND ASC
> "pmud" wrote:
>|||Please post your DDL with INSERT statements of the sample data and desired
output.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:9D434774-65A1-4C29-8415-23309F77BF08@.microsoft.com...
Hi,
I have already tried that. It doesnt even display the NULL value. I wabt to
display those values where the data is <NULL> , but not those where the
field
is blank.. i mean where the cell is completely blank.
Any other ideas?
Thanks
--
pmud
"KH" wrote:
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T' AND BRAND <> ''
> ORDER BY BRAND ASC
> "pmud" wrote:
>|||Try
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
AND COALESCE(BRAND,'this value is null') <> ''
ORDER BY BRAND ASC
HTH,
Stu|||Try this also
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
AND ltrim(rtrim(BRAND)) <> ''
ORDER BY BRAND ASC|||Hi Stu,
This works. :)) can you please explain how it works...
Thanks
--
pmud
"Stu" wrote:

> Try
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T'
> AND COALESCE(BRAND,'this value is null') <> ''
> ORDER BY BRAND ASC
> HTH,
> Stu
>|||Sure!
COALESCE looks at a given list of values or columns, and returns the
first NON-null expression, so for every row it looks at BRAND to
determine if it's null, ad if it's not then it returns the absolute
expression 'this value is null'. You could have put any absolute
expression in there (e.g., 'whats up Doc?'), and it would have returned
that value when it encountered a NULL in the BRAND column. This
allowed SQL Server to compare a replacement value ('this value is
null') with an empty string, instead of comparing a NULL value to an
empty string, eg.
NULL <> '' = NULL; not included in the result set
'this value is null' <> '' = TRUE; included in the result set
Since the COALESCE was in the WHERE clause, and not the SELECT clause,
the SELECT returned the value where the second expression in the
COALESCE statement was used (NULL).
Boy, I read that 5 times, and I don't think I can make it any clearer.
Perhaps someone else can explain it better than I; I just know how to
make it work :)
Stu|||Hi Stu,
Thanks for taking the time to explain it. it was helpful. Combined with what
you explained, I read on Coalsce and I understand it now. :)) .. I am sure
had I read it without u explaining it, I would have never understod.
Thanks
--
pmud
"Stu" wrote:

> Sure!
> COALESCE looks at a given list of values or columns, and returns the
> first NON-null expression, so for every row it looks at BRAND to
> determine if it's null, ad if it's not then it returns the absolute
> expression 'this value is null'. You could have put any absolute
> expression in there (e.g., 'whats up Doc?'), and it would have returned
> that value when it encountered a NULL in the BRAND column. This
> allowed SQL Server to compare a replacement value ('this value is
> null') with an empty string, instead of comparing a NULL value to an
> empty string, eg.
> NULL <> '' = NULL; not included in the result set
> 'this value is null' <> '' = TRUE; included in the result set
> Since the COALESCE was in the WHERE clause, and not the SELECT clause,
> the SELECT returned the value where the second expression in the
> COALESCE statement was used (NULL).
> Boy, I read that 5 times, and I don't think I can make it any clearer.
> Perhaps someone else can explain it better than I; I just know how to
> make it work :)
> Stu
>