Click here to Skip to main content
15,390,740 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!
   
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
   
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. :)
satpal 2 6-Nov-12 8:42am
   
ok thanks for this :)
SELECT COUNT(column_to_count) AS count FROM table
   
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