Find all records which contain both null and empty columns

Question

SQL is not really my strong point, so please excuse my ignorance.

This query returns all records which contain null values

;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as ns)
SELECT *
FROM   Analytics
WHERE  (SELECT Analytics.*
        FOR xml path('row'), elements xsinil, type
        ).value('count(//*[local-name() != "colToIgnore"]/@ns:nil)', 'int') > 0

Source: Find All Rows With Null Value(s) in Any Column

The above query returns a list of records which have null values. How do I modify this query to return records which contains both null or empty columns.

And more important, are you really a Navy Seal? And if so, what do SQL Server databases have to do with MP5s? Shouldn't you be drilling right about now? :) — Alvin Thompson, Jun 16 '14 at 18:32

score 0 · Accepted Answer · answered Jun 16 '14 at 18:55

You can try the following

;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as ns)
SELECT *
FROM   Analytics
WHERE  (SELECT Analytics.*
        FOR xml path('row'), elements xsinil, type
        ).value('count(//*[local-name() != "colToIgnore"]/@ns:nil)', 'int') > 0
        OR
        (SELECT Analytics.*
        FOR xml path('row'), elements xsinil, type
        ).value('count(//*[text()=""])', 'int') > 0

Find all records which contain both null and empty columns

1 Answers1