How to Combine columns with NULL values in SQL 2008 R2 using coalesce
I ran into a problem where a NULL value was preventing the rest of the other columns I was trying to combine fail for UPS WorldShip interface. I discovered that coalesce would be my solution for the issue. I have profided a working sample below for reference:
/****** Object: View [dbo].[UPS_View] Script Date: 05/10/2011 03:16:03 ******/
SET
ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
VIEW [dbo].[UPS_View]
AS
SELECT
dbo.ac_Orders.OrderNumber, coalesce(dbo.ac_OrderShipments.ShipToCompany,'') + ' ' + coalesce(dbo.ac_OrderShipments.ShipToFirstName,'') + ' ' + coalesce(dbo.ac_OrderShipments.ShipToLastName,'') AS [ShipToName], dbo.ac_OrderShipments.ShipToAddress1, dbo.ac_OrderShipments.ShipToAddress2,
dbo
.ac_OrderShipments.ShipToCity, dbo.ac_OrderShipments.ShipToProvince, dbo.ac_OrderShipments.ShipToPostalCode,
dbo
.ac_OrderShipments.ShipToCountryCode, dbo.ac_OrderShipments.ShipToResidence
FROM
dbo.ac_Orders INNER JOIN
dbo
.ac_OrderShipments ON dbo.ac_Orders.OrderId = dbo.ac_OrderShipments.OrderId
GO
Article ID: 72, Created On: 5/10/2011, Modified: 11/1/2013