ISNULL Concatenation on SQL Server

When concatenating multiple fields, the concatenated field will be returned nil by default if any of the concatenated fields are NULL.

To work around this and get a value for the concatenated fields with values, you can use the ISNULL function on each field being concatenated.

SELECT dbo.ITEMS.ITEMNO, dbo.BRAND.DESCRIPT AS BrandDescript, dbo.ITEMS.MODEL, dbo.ITEMS.DESCRIPT AS ItemDescript, dbo.CATEGORY.DESCRIPT AS CatDescript
, ISNULL(dbo.BRAND.DESCRIPT,'') + ' ' + ISNULL(dbo.ITEMS.MODEL,'') + '' + ISNULL(dbo.ITEMS.DESCRIPT,'') + ' ' +ISNULL(dbo.CATEGORY.DESCRIPT,'') AS FullDescript, ISNULL(dbo.BRAND.DESCRIPT,'') + ' ' + ISNULL(dbo.ITEMS.MODEL,'') + '' + ISNULL(dbo.CATEGORY.DESCRIPT,'') AS BMC
FROM dbo.ITEMS, dbo.CATEGORY, dbo.BRAND
WHERE dbo.ITEMS.CATEGORY = dbo.CATEGORY.CATEGORYAND dbo.ITEMS.BRAND = dbo.BRAND.CODE