Concatenating fields which may have a null 1

[Sashanan]

In an SQL Server 7.0 database, I'm trying to concatenate a couple of fields into one string. The problem is that one of them is often Null, and doing so causes the entire string to default to Null; which makes sense but is not what I want. Rather, I want it to be treated like an empty value in that case.

So, when I do:

SELECT col1 + col2 + col3 AS fullstring FROM MyTable WHERE [some condition]

And the record this picks up has:

col1: A
col2: [null]
col3: C

I want the result to be:

fullstring: AC

whereas currently it becomes Null.

How can I accomplish this?


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[JamesLean]

SELECT COALESCE(col1, '') + COALESCE(col2, '') + COALESCE(col3, '') AS fullstring
FROM mytable

You can also use the SQL-specific function ISNULL. Or even issue the following statement before your original query:

SET CONCAT_NULL_YIELDS_NULL OFF

--James

 

[Sashanan]

Plenty of options I see. Thanks!


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)