how to exclude blank fields from resultset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to exclude blank fields from resultset

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-06 : 16:41:37

i have a query:

select ordernumber,amount from orders
where ordernumber is not null

but i am getting some results where, ordernumber is still blank

ORDERNUMBER Amount
11 1200
12 500
400
15 600

how do i make sure that the fields where order number is blank does not comes in the result.

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 17:16:34

You can also include a condition where DATALENGTH(Amount) > 0.
It would be a cleaner solution if you had a default of 0 to the amount column so yor query would simply have a WHERE Amount > 0. Then you wouldnt have to check for NULLs or BLANKs.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-07 : 10:25:49

I am not talking about amount, its the order ID.

sometimes the orderID is blank but the amount is there.

Consider this.

ORDERID, AMOUNT
AAAA, 100
BBBB, 200
CCCC, 300
, 200 -----DON"T WANT THIS AMOUNT IN THE TOTAL
DDDD, 3000
, 2000 ------DON"T WANT THIS ONE AS WELL

NOw select sum(amount) from orders where Orderid is not null

does includes the rows with 200 and 2000. What is the other way to exclude this. I am not talking about amount and OrderID is varchar

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-07 : 11:37:29

The idea here is to use DATALENGTH(Column)>0. IF you are after OrderID column instead of Amount column all you have to do is use the OrderID column.

 
SELECT SUM(amount) 
FROM Orders
WHERE orderid IS NOT NULL AND datalength(orderid) > 0

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources