CODEWITHSUNDEEP
sql-server-2005t-sql
Arnkrishn
Arnkrishn

Reputation: 30424

Any better ways to ascertain whether a column in a table is empty or not?

I have a table say T in SQL Server 2005 database and it has two columns say A and B, which more often than not won't have any values in them. How to check whether A and B are empty (has all zero length strings) or not?

I have this naive way of doing it - 

select count(*) as A_count from T where A <> ''

Let's assume A has data type varchar.

I was wondering whether I can get the same information using a system table, and if so would that be faster than this query?

cheers

Upvotes: 0

Views: 155

Answers (3)

A-K
A-K

Reputation: 17080

If your column is nullable, you will have to modify your query as follows:

select count(*) as A_count from T where COALESCE(A, '') <> ''

otherwise you will not count nulls.

Upvotes: 0

dpmattingly
dpmattingly

Reputation: 1321

If we are talking about zero-length strings, then this is the way that I would do it:

select count(*) as A_count from T where LEN(A) > 0

Keep in mind, however that if A could be null, then these rows will not be caught by either LEN(A) > 0 or LEN(A) = 0, and that you will have to wrap an ISNULL around A in that case.

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171371

Your method is essentially correct, although the wording in your question is imprecise. Does empty include NULL or a non-zero length empty string?

You could handle those cases with:

select count(*) as A_count from T where isnull(rtrim(ltrim(A)), '') <> ''

Also, make sure there is an index on column A.

Upvotes: 2

Related Questions