I have this simple-minded query:
select a.str_number+' '+a.str_name+' '+a.str_suffix+' '+a.str_apt AS addr from mytable
This query works as long as none of the concatenated values are not NULL. If any is null, the address does not show.
I tried this:
select IsNULL(a.str_number+' '+a.str_name+' '+a.str_suffix+' '+a.str_apt AS addr,'') from table
What I would like to accomplish is to replace NULL with empty space if the value is null but I still get no values.
This is a consequence of Ted Codd's rules for relational databases. As applied to MS SQL
Ideally, any operation working with NULL should lead to NULL. Though there were some issues in past due to which some SQLServer options are in place which decides how NULL is handled (instead of handling it the "definite" way).
ANSI_NULLS: If OFF, then NULL = NULL is True for the comparison. If ON (the default), NULL = NULL returns UNKNOWN (ideal situation).
CONCAT_NULL_YIELDS_NULL: If ON, NULLs are treated ideal way. e.g. NULL + <numValue> = NULL. If OFF, NULLs are treated in a nonstandard way such that NULL + <Value> = <Value>. (done for backward compatibility as per BOL, which says it all)
Null basically means something like: We don't know the value of this and so we shouldn't ever try to use it. I believe SQL distinguishes between null and an empty string, so I'd store an empty string "" if that is what I later want to use in concatenation.
ISNULL each column which may contain a null value and concatenate the results.SELECT
ISNULL(a.str_number,'') + ' ' +
ISNULL(a.str_name,'') + ' ' +
ISNULL(a.str_suffix,'') + ' ' +
ISNULL(a.str_apt,'')
AS addr
FROM mytable
Very ugly but that should do it.
Use COALESCE:
select a.str_number+' '+a.str_name+' '+COALESCE(a.str_suffix, '')+' '+a.str_apt AS addr from mytable
