SQLTeam.com | Weblogs | Forums

Address block with null fields skipped

sql2012

#1

Hello,
I'm trying to build an address block but in some cases Address and AddressNo are null. After every field that is not null I need a "," to be added. If the value is null (or empty string) the field and "," must be skipped.
Here's an example:

No null --> Address,AddressNo,PostalCode,Village,District
Address & AddressNo = null --> PostalCode,Village,District

Below is my code:

CREATE PROCEDURE [dbo].[GetAddress]
        ( @IDNO nvarchar(20),
          @Username nvarchar(256))
        AS
        BEGIN

	SET NOCOUNT ON;

	WITH cteDepartment(Department)
	AS
	(
	SELECT Department.Department
	FROM  Users INNER JOIN  Department
	ON Users.DepartmentID= Department.DepartmentId
	WHERE Users.UserName=@Username
	)

	SELECT Department, IDNO, CONCAT(PostalStreet,',',PostalNo,',',CONVERT(NVARCHAR(10),PostCode),',',PostCommunity,',',PostDistrict) AS AddressBlock
	FROM Customers CROSS JOIN  cteDepartment
	WHERE IDNO=@IDNO
END

Thank you in advance.


#2

This is what I did but works only with null values and not empty strings...

CONCAT(ISNULL(CONCAT(PostalStreet,', '),''),ISNULL(CONCAT(PostalNo,', '),''),CONVERT(NVARCHAR(10),PostCode),', ',PostCommunity,', ',PostDistrict) AS AddressBlock


#3
STUFF(
    COALESCE(', ' + NullIf(PostalStreet, ''), '')
    + COALESCE(', ' + NullIf(PostalNo, ''), '')
    + COALESCE(', ' + CONVERT(NVARCHAR(10),PostCode), '')
    + COALESCE(', ' + NullIf(PostCommunity, ''), '')
    + COALESCE(', ' + NullIf(PostDistrict, ''), '')
    , 1, 2, '') AS AddressBlock

#4

Perfect, thank you!