I have about 20 columns and want to filter all of them together having only 'not null' values. Is there a way to do this in sql since I don't want to mention all column names in my query.
Something like this -
Select * from table_name where columns IS NOT NULL
For SQL Server, if you can handle returning an "extra" column, you can do something like this:
;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns)
SELECT v.*
FROM ( SELECT t.*
, (SELECT t.*
FOR xml path('row'), elements xsinil, type
).value('count(//*/@ns:nil)', 'int') AS NullCount
FROM table_name t
) v
WHERE v.NullCount = 0
I couldn't get the NullCount expression into a HAVING clause, this was as close as I could come. So this returns an extra NullCount column.
Tested on SQL Server 2008
CREATE TABLE foo
( id INT NULL
, col2 INT NULL
, col3 VARCHAR(10) NULL
, col4 DATE NULL
, col5 DECIMAL(14,5) NULL
);
INSERT INTO foo (id, col2, col3, col4, col5) VALUES
(1,NULL,NULL,NULL,NULL)
,(2,2,'2','2/2/2012',22.22)
,(3,3,'3','3/3/2013',333.333)
,(4,4,NULL,'4/4/2014',4444.4444)
,(5,5,'5',NULL,55555.55555)
,(6,6,'6','6/6/2016',NULL)
;
;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns)
SELECT t.*
, (SELECT t.*
FOR xml path('row'), elements xsinil, type
).value('count(//*/@ns:nil)', 'int') AS NullCount
FROM foo t
;
;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns)
SELECT v.*
FROM ( SELECT t.*
, (SELECT t.*
FOR xml path('row'), elements xsinil, type
).value('count(//*/@ns:nil)', 'int') AS NullCount
FROM foo t
) v
WHERE v.NullCount = 0
;
mysql?where foo is not null and bar is not null and baz is not null and ....