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
GOAssuming 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] OFFSELECT
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