SQL Server string concatenation

[David]

Hi all,

I have a small problem...

I need to do string concatenation in sql server...

i.e.

select field1 + field2 + field3 as JoinedField from table

This works fine IF all the fields have content, but if one of them is null,
then the whole JoinedField is null.

Any idea on how I can fix it? I want the joined value as though the null was
just an empty string.

Thanks.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available

 

[Just Me]

Yeah

IsNull( field1, WhatYouWantGoesHere )

 

[David]

Cool, thank you. That works great...
--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available

 

[Cowboy \(Gregory A. Beamer\)]

select
CASE WHEN field1 is null then ''
ELSE field1 END
+
CASE WHEN field2 is null then ''
ELSE field2 END
+
CASE WHEN field3 is null then ''
ELSE field3 END AS JoinedField

 

[Paul Shapiro]

And a 3rd version (all 3 would work fine):

Select Coalesce(field1, '') + coalesce(field2, '') etc.