CODEWITHSUNDEEP
sql-servervb.net
Chidi Okeh
Chidi Okeh

Reputation: 1557

Handling Null Strings When Concatenating Select Statement

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.

Upvotes: 0

Views: 565

Answers (3)

Damien Dennehy
Damien Dennehy

Reputation: 4064

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.

Upvotes: 1

Arne Lund
Arne Lund

Reputation: 2446

Use COALESCE:

select a.str_number+' '+a.str_name+' '+COALESCE(a.str_suffix, '')+' '+a.str_apt AS addr from mytable

Upvotes: 0

RedGrittyBrick
RedGrittyBrick

Reputation: 4002

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.

Upvotes: 1

Related Questions