Skip to content

Counting Null Values in a Row

April 9, 2012

On StackOverflow today someone asked how to get the number of null values in a table’s row; i.e. if there are 10 columns, and 4 of the columns contain data, the answer would be 6.

The requirement was that it would work in sqlite, which I don’t have a generic solution for, but it did prompt me to play around with this in SQL Server.  The result is fairly tidy:

with xmltab(xmlcol) as
    (select cast(
            myIdCol = 1
        for xml path('MYTABLE'), elements xsinil
    ) as xml) as xmlcol
select cast(cast(xmlcol.query('count(//*[@xsi:nil="true"])') as varchar) as int) from xmltab
About these ads

From → sql server

One Comment
  1. I was just looking for this information for some time. After 6 hours of continuous Googleing, at last I got it in your web site. I wonder what’s the lack of Google strategy that do not rank this kind of informative web sites in top of the list. Normally the top websites are full of garbage.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

%d bloggers like this: