Click here to Skip to main content
15,918,808 members
Search within:


How to Count blanks in any particular column of table in sql server 2005?
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
Add a Solution

3 solutions

SELECT COUNT(column_to_count) AS count FROM table
 
Share this answer
 
Posted
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"
SQL
SELECT COUNT(Column_Name) FROM TableName WHERE ltrim(rtrim(Column_Name)) = ''


Hope this helps!
 
Share this answer
 
Posted
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
 
Posted
Updated 6-Nov-12 2:42am
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. :)
Add a Solution

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

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
count similar rows of particular column
Non blank cell count for a particular row in dynamic colums in ssrs
Regarding Calculation in a particular column in a table of SQL Server database
How can I bifurcate the tables regarding blank or having data SQL Server 2005
The Columns cannot be created in sql server 2005
Count items in column? (SQL)
procedure for create a table in sql server 2005
How to insert this table columns and rows in SQL server with ASP.NET
Count items in column by date? (SQL)
Count on a table with joins

Advertise
Privacy
Cookies
Terms of Use
Last Updated 6 May 2021
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-06-13:1

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