imagine there are 50 columns. I dont wan't any row that includes a null value. Are there any tricky way?
SQL 2005 server
2
-
You don't want any null values in ANY of the 50 columns?dotariel– dotariel2010-11-04 14:32:06 +00:00Commented Nov 4, 2010 at 14:32
-
yeah.. The valid rows must have all columns filled ..Bilgin Kılıç– Bilgin Kılıç2010-11-04 14:34:25 +00:00Commented Nov 4, 2010 at 14:34
Add a comment
|
4 Answers
Sorry, not really. All 50 columns have to be checked in one form or another.
Column1 IS NOT NULL AND ... AND Column50 IS NOT NULL
Of course, under these conditions why not disallow NULLs in the first place by having NOT NULL in the table definition
Comments
If it's SQL Server 2005+ you can do something like:
SELECT fields
FROM MyTable
WHERE stuff
EXCEPT -- This excludes the below results
SELECT fields
FROM MyTable
WHERE (Col1 + Col2 + Col3....) IS NULL
Adding a null to a value results in a null, so the sum of all your columns will be NULL.
This may need to change based on your data types, but adding NULL to either a char/varchar or a number will result in another NULL.
1 Comment
JNK
@gbn - Yeah I thought of that too, and added it to the end.
If you are looking at the values not being null, you can do this in the select statement.
SELECT ISNULL(firstname,''), ISNULL(lastname,'') FROM TABLE WHERE SOMETHING=1
This will replace nulls with string blanks. If you want another value use: ISNULL(firstname,'empty') for example. You can use anything where the word empty is.
1 Comment
JNK
I think he wants to not have any rows in his result set where any value is
NULL.I prefer this query
select *
from table
where column1>''
and column2>''
and (column3>'' or column3<'')
Allows sql server to use an index seek if the proper index/es exist. you would have to do the syntext for column 3 for any numeric values that could be negative.
2 Comments
JNK
...except any inequality comparison to
NULL will always return false, since NULL is not a value but an absence of a value. Try SELECT 'OK' Where 2 > NULL and see what you get :)
DForck42
yes, it always returns false for nulls, but any other value works fine (you could always make it >= to compensate for empty non-null strings), hence getting rid of all records with any nulls.