Help with Concatenating fields

[johnsun1976]

Hi.. I have a problem this SQL statement

SELECT Desc1 + ' ' + Desc2 AS Description FROM Item WHERE ItemID = 1234

if Desc1 or Desc2 is NULL, Description is null.. Does anyone know how to fix this?
Thanks
John

 

[tlbroadbent]

Method 1:
SELECT
Isnull(Desc1,'') + ' ' +
Isnull(Desc2,'') AS Description
FROM Item WHERE ItemID = 1234

Method 2: Turn off ANSI default concatenation behavior
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT
Desc1 + ' ' + Desc2 AS Description
FROM Item WHERE ItemID = 1234
SET CONCAT_NULL_YIELDS_NULL ON

Using either method, you may want to add the LTRIM and RTRIM functions to trim leading and trailing spaces. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[jitter]

You could use the IsNull() on each field and return empty string if it is. I think this would prevent that anything concatenated with a null will be null.

 

[johnsun1976]

Thanks.. That worked..
John