THis shoudl be easy, but I am drawing a blank - null
-
I need to return all the values in a table that are not null or empty. The actual values in that column are "null" or no value at all, in other words it is empty.
Part of it is
where column is not null
but this still returns the empty value rows
how do I do this. thanks
-
If I understand your question....
where column is not null
and column <> ' '
Steve
-
Or try
where len(column) > 0
-
as you have noticed NULL <> ' '
If there is no symantic difference for your schema and apps, avoid the NULL.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Or use the isnull function...
where isnull(column, '') <> ''
Regards,Iain
-
Two comments on prior posts:
a. Try to avoid "where somefunction(x) = somevalue." (E.g., do not use the "isnull(column,'')" suggestion. That pattern in where clauses often causes SQL Server to evaluate the expression for EVERY row in the table, thus degrading performance. The first suggestion is better.
b. On NULL v. empty strings. I agree you should not have both NULL and empty string to basically mean the same thing. However, I find *most* who advocate not using NULLs do not understand the arguments for and against well.
Would you rather have "three-valued logic" (true/false/unknown)? Or would you rather have "18-valued logic" (n-valued)? That is, where there is a "special value" for each of your columns that really means "unknown". I have found in practice that NULLs offer a much cleaner predictable solution to the problem.
The only "real" solution to not using NULLs is to decompose your entity design such that every related group of optional fields are in their own table. (See C.J. Date's writings.) Most NULL-abolitionists, however, do *NOT* suggest that!
Instead, they would use "special" values for each "optional" column. That leads to more confusion, still has the special logic requirements, and skews data analysis. Generally, it's a very poor idea.(I have seen entire marketing campaigns invalidated by using "special" defaults for unknown. Problems arose when those special defaults could also be considered actual values. E.g., was the price ("0.0") unknown? Or was it a free product?)
-
Quote from previous:
The only "real" solution to not using NULLs is to decompose your entity design such that every related group of optional fields are in their own table. (See C.J. Date's writings.) Most NULL-abolitionists, however, do *NOT* suggest that!
Instead, they would use "special" values for each "optional" column. That leads to more confusion, still has the special logic requirements, and skews data analysis. Generally, it's a very poor idea.For those who have not done this. It works best when there are numerous NULLs to work against and especially with fixed width datatypes such as char as you save the space cost. However the reason the "NULL-abolitionists" don't like is on a join of the two tables they get a NULL which is what they don't want.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply