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?
Updated 6-May-21 9:00am

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

Hope this helps!
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.

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

Hope that helps. If it does, mark answer as solution and/or upvote.
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
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