SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Monday, July 24, 2006

find null value in any column

SQL Apprentice Question
Assume I have a table with only 1 row and all column is 'int'. I just
wonder is it possible to use SQL to check if any one of those column
contain a 'null' without knowing the column name?


For example,


create t1 ( i1 int null, i2 int null ....)
insert into t1 (i1, i2, ...) values (1, 1, 1, ... , null, 1, ... null,
..)


and check if any of those column contain null...



Celko Answers
>> Assume I have a table with only 1 row and all column is INTEGER. I just wonder is it possible to use SQL to check if any one of those column contain a 'null' without knowing the column name? <<


SELECT 'yes'
FROM Foobar
WHERE (c1 + c2 + c3+ .. + cn) IS NULL;

No comments: