Select column names whose entries are not null
Create from the INFORMATION_SCHEMA.COLUMNS
table a string that contains the SQL you wish to execute, then prepare a statement from that string and execute it.
The SQL we wish to build will look like:
SELECT 'column_a'
FROM table_name
WHERE `column_a` IS NOT NULL
HAVING COUNT(*)
UNION ALL
SELECT 'column_b'
FROM table_name
WHERE `column_b` IS NOT NULL
HAVING COUNT(*)
-- etc.
(One could omit the WHERE
clause and substitute COUNT(*)
for COUNT(column)
, but I think that might be less efficient on indexed columns).
This can be done using the following:
SET group_concat_max_len = 4294967295;
SELECT GROUP_CONCAT(
' SELECT ',QUOTE(COLUMN_NAME),
' FROM table_name',
' WHERE `',REPLACE(COLUMN_NAME, '`', '``'),'` IS NOT NULL',
' HAVING COUNT(*)'
SEPARATOR ' UNION ALL ')
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'table_name';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See it on sqlfiddle.
Related videos on Youtube
Baz
I am a Bioinformatics Software Engineer working mainly with Java, MySQL and JavaScript. I received my MSc in Computer Science in 2012 for my studies with a strong focus on data mining. In my spare time, I like to develop highly functional but lightweight applications in Java. I mainly develop in Java, but I usually use whatever language/tool is necessary to get the job done. I'm interested in Android development, new web technologies, data visualization and good beer. LinkedIn profile Careers profile Also: Top SWT answerer of all time 1st person to achieve the gold SWT badge 1st person to achieve the silver SWT badge 3rd person to achieve the bronze SWT badge
Updated on June 04, 2022Comments
-
Baz almost 2 years
I would like to have a list of those columns of a table that have at least one non-
NULL
data entries in them.In other words, I would like to get the column names for which the following returns at least one entry:
SELECT DISTINCT column_name FROM table WHERE column_name IS NOT NULL
I tried the following:
SELECT column_name FROM information_schema.columns WHERE table_name = "table_name" AND EXISTS ( SELECT DISTINCT column_name FROM table_name WHERE column_name IS NOT NULL )
But this also returns the column names where all the entries are
NULL
.So how do I get only those columns with non-
NULL
entries? -
DRapp over 11 yearsSorry... Viewing on iPad and thought it registered... Definitely worth the up vote :)