Excluding Empty Strings for SELECT DISTINCT 1

[Cheryl3D]

Hello:

I am using SQL Server 2000 to generate the following SELECT DISTINCT SQL statement:

SELECT DISTINCT FundSites.RCCC
FROM FundSites
ORDER BY FundSites.RCCC

At first when I ran this in the Query Analyzer, I received the single column of distinct RCCCs, but at the top of the column list I received a NULL, and blank result (top 2 rows of the list).

So to get rid of the one NULL record I added more the the SQL statement and ran:

Select Distinct FundSites.RCCC
From FundSites
WHERE FundSites.RCCC Is Not Null
Order By FundSites.RCCC

That worked and got rid of the NULL record, but I still have a blank (empty string -- I think)row at the top of the list.

I tried the following SQL:

Select Distinct FundSites.RCCC
From FundSites
WHERE FundSites.RCCC Is Not Null or FundSites.RCCC <> ''
Order By FundSites.RCCC

Result -- I still got the one blank record at the top of the RCCC column.

How can I get the SQL statement to return the distinct values (RCCCs) without that blank or empty string record at the top?

Once again, any help would be greatly appreciated.

Thanks,
Cheryl

 

[sunila7]

Hi,

Try this...

Select Distinct FundSites.RCCC
From FundSites
WHERE (FundSites.RCCC Is Not Null or rtrim(ltrim(FundSites.RCCC)) <> '')
Order By FundSites.RCCC


Sunil

 

[Cheryl3D]

Thanks Sunil:

I tried your SQL statement but got the same result (I still got the one blank record at the top of the RCCC column).

However, I think that I may be able to remedy this problem another way.

A lot of the blank RCCCs belong to old records in the database from previous years data (2000,2001, etc.)

So can't I use an UPDATE statement to replace all the blank (empty string)RCCC records in the FundSites table with the actual NULL or <NULL> recognized in SQL Server?

Can't I use SQL like:

UPDATE FundSites
SET RCCC = NULL
WHERE RCCC = ''

Please let me know if this a good fix for this problem.

Thanks again,
Cheryl

 

[sunila7]

Oops...

change ur SQL like this....

Select Distinct FundSites.RCCC
From FundSites
WHERE (FundSites.RCCC Is Not Null and rtrim(ltrim(FundSites.RCCC)) <> '')

And ur update stmnt shld work...

Sunil

 

[Cheryl3D]

Thank you very much Sunil
Works just great!!!

Cheryl