Combining address fields into 1 address field - bullet proof method.

  • I often have to combine multiple address fields into 1 for a variety of reasons.   I can hack a calculation that works for each case.  But I'm trying to learn the optimal method and possibly develop a bullet proof calculation.

    Set up code

    USE [AdventureWorksLT2012]
    GO
    /****** Object: Table [SalesLT].[Client]  Script Date: 27/04/2018 16:45:33 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [SalesLT].[Client](
        [CustomerID] [int] IDENTITY(1,1) NOT NULL,
        [Address1] [varchar](50) NULL,
        [Address2] [varchar](50) NULL,
        [City] [varchar](50) NULL,
        [PostCode] [varchar](15) NULL
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [SalesLT].[Client] ON
    GO
    INSERT [SalesLT].[Client] ([CustomerID], [Address1], [Address2], [City], [PostCode]) VALUES (2, N'Morris House', N'Arley Road', N'Liverpool', N'L2 3ER')
    GO
    INSERT [SalesLT].[Client] ([CustomerID], [Address1], [Address2], [City], [PostCode]) VALUES (3, N'140 Hoole Road', N'Chester', NULL, N'CH10 3ER')
    GO
    INSERT [SalesLT].[Client] ([CustomerID], [Address1], [Address2], [City], [PostCode]) VALUES (4, N'12 James Street', NULL, N'Liverpool', N'L1 3TR')
    GO
    INSERT [SalesLT].[Client] ([CustomerID], [Address1], [Address2], [City], [PostCode]) VALUES (5, NULL, N'120 James Street', N'Liverpool', N'L1 3TR')
    GO
    SET IDENTITY_INSERT [SalesLT].[Client] OFF
    GO

    Assuming Address1 is never null I can use this code to combine the address fields. 


    SELECT CONCAT(
        ISNULL(c.Address1, '') ,
        ISNULL(', ' + c.Address2, ''),
        ISNULL(', ' + c.City, ''),
        ISNULL(', ' + c.PostCode, '')) AS MailAddress
    FROM SalesLT.Client AS c;

    I'm trying to stretch my TSQL skills hence attempting to create calculation that will take into account null Address1

    The only thing I can think of is to use a CASE for Address2.  Even though ISNULL is similar to CASE it doesn't give me the capability to check another field.  I seem to recall ISNULL is semantic sugar for CASE so I guess there will be no difference in performance.


    SELECT CONCAT(
        ISNULL(c.Address1, '') ,
        CASE WHEN c.Address1 IS NULL THEN ISNULL(c.Address2, '')
          ELSE ISNULL(', ' + c.Address2, '')
        END,
        ISNULL(', ' + c.City, ''),
        ISNULL(', ' + c.PostCode, '')) AS MailAddress
    FROM SalesLT.Client AS c;

    The top query doesn't work with a NULL in Address1.  The second query works.   Is there better way to make a bullet proof calculation?   Would a UDF be of benefit.   Or even a c# function in the CLR?

    The table size can be millions of rows so I'm thinking of the ultimate efficiency.  I suppose I can use a CASE for each field.  So the only question is there anything better than a CASE for this problem?

    [/code]

  • You could use the old stuff method:
    STUFF(ISNULL(', ' + Addr1,'') + ISNULL(', ' + Addr2,'') + ISNULL(', ' + Addr3,'') + ISNULL(', ' + Addr4,'') + ISNULL(', ' + Pcode,''),1,2,'')

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • I'm not sure there's a good way to avoid it.   Do you need to eliminate a comma between the City and postcode ?  Because you appear to have address data that is in the category of what I'll refer to as "mal-formed".   You've got an address with a City of Chester, but it's not in the right data column.   That probably needs fixing before you have much of a shot at getting a consistent result.   The cost of CASE might not be bad unless you're looking for really super fast results.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Just noticed something in the post. ISNULL isn't the same as a CASE, that's COALESCE. ISNULL and COALESCE do operate different; especially when different data types are involved in the parameters.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Given that in many cases you need a linebreak in order to appropriate format an address (like, say to actually use it to mail something), and that you can have extra commas in the actual data in, the initial reaction is  - don't preassemble it.  Wait until you KNOW what you're using it for and then format it appropriately.

    That said - if you have a format that is far and away the most common for your case and just want to save the time on the way out, use any of the formulae provided that return the appropriate result, and add it to your table as a persisted calculation column.  The refer to the name of the column on the way out when you need it.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Suggest you use the CONCAT function as it ignores the NULL values.
    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;

    IF OBJECT_ID(N'dbo.Client') IS NOT NULL DROP TABLE dbo.Client;
    CREATE TABLE [dbo].[Client](
      [CustomerID] [int] IDENTITY(1,1) NOT NULL,
      [Address1] [varchar](50) NULL,
      [Address2] [varchar](50) NULL,
      [City] [varchar](50) NULL,
      [PostCode] [varchar](15) NULL
    );
    SET IDENTITY_INSERT [dbo].[Client] ON
    INSERT [dbo].[Client] ([CustomerID], [Address1], [Address2], [City], [PostCode])
    VALUES
    (2, N'Morris House', N'Arley Road', N'Liverpool', N'L2 3ER')
    ,(3, N'140 Hoole Road', N'Chester', NULL, N'CH10 3ER')
    ,(4, N'12 James Street', NULL, N'Liverpool', N'L1 3TR')
    ,(5, NULL, N'120 James Street', N'Liverpool', N'L1 3TR');
    SET IDENTITY_INSERT [dbo].[Client] OFF

    SELECT
     CONCAT
      (
       CL.Address1
       +CHAR(44)
       ,CL.Address2
       +CHAR(44)
       ,CL.City
       +CHAR(44)
       ,CL.PostCode
      ) AS FULL_ADDRESS
     ,CL.CustomerID
     ,CL.Address1
     ,CL.Address2
     ,CL.City
     ,CL.PostCode
    FROM dbo.Client CL;

    Output


    FULL_ADDRESS           CustomerID Address1    Address2    City   PostCode
    ------------------------------------------ ----------- ----------------- ------------------ ---------- ---------
    Morris House,Arley Road,Liverpool,L2 3ER 2    Morris House  Arley Road   Liverpool L2 3ER
    140 Hoole Road,Chester,CH10 3ER    3    140 Hoole Road  Chester    NULL   CH10 3ER
    12 James Street,Liverpool,L1 3TR    4    12 James Street NULL     Liverpool L1 3TR
    120 James Street,Liverpool,L1 3TR    5    NULL     120 James Street Liverpool L1 3TR

  • Thanks for the input I'll stick with suggestion for persisted column.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply