Click here to Skip to main content
15,867,860 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
How to Count blanks in any particular column of table in sql server 2005?
Posted
Updated 6-May-21 9:00am
v2

SQL
SELECT COUNT(Column_Name) FROM TableName WHERE ltrim(rtrim(Column_Name)) = ''


Hope this helps!
 
Share this answer
 
Comments
Ankur\m/ 6-Nov-12 8:46am    
Just a quick note - Avoid using ltrim,rtrim and handle your code to pass empty value. When the table becomes big, ltrim-rtrim impacts query performance.
Since your question seemed like you needed it just for statistics, I added ltrim-rtrim for exact count.
Following query should do the job for you.

SQL
select count(*) 
from tableName 
where (columnName is null or columnName = '')


Hope that helps. If it does, mark answer as solution and/or upvote.
Thanks
Milind
 
Share this answer
 
v2
Comments
satpal 2 6-Nov-12 8:37am    
it is not working for null value counting!!
Ankur\m/ 6-Nov-12 8:41am    
See my comment to him. The query is wrong.
Btw you never mentioned about null checks in your question.
Ankur\m/ 6-Nov-12 8:40am    
Your Query is InCorrect! It won't count the null values.
The correct query is:
select count(*)
from tableName
where (columnName IS null or columnName = '')
rather '= null'
MT_ 6-Nov-12 8:43am    
updated query. Thanks Ankur. Generally we do check for both null and '' to get count for blank column values.
Ankur\m/ 6-Nov-12 8:50am    
Generally we do check for both null and '' to get count for blank column values.
I am sorry but I disagree. Why do I put a null check in the column where I do not allow nulls? And remember an extra check means more effort and time taken for executing the query. :)
SELECT COUNT(column_to_count) AS count FROM table
 
Share this answer
 
Comments
CHill60 7-May-21 5:20am    
That will count non-null values in [column_to_count]. Nine years ago the OP wanted to count blanks. And don't suggest using
SELECT COUNT(*) - COUNT(column_to_count) FROM table
as that still won't count the "blanks"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900