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(
        (select
            *
        from
            myTable
        where
            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:

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: