jeff.born (10/21/2016)
This works great for the addresses that have a street suffix in them, however I have many addresses like this:
INSERT INTO [dbo].[Address] ([Address]) VALUES ('48 Martin Luther King');
There is no "King" street suffix, so I end up with a null for the tweaked address. As far as I can tell that is the only issue. Is there a way to address those situations in the code below?
drew.allen (10/20/2016)
I tweaked the code (added some REVERSEs).
SELECT Address_pk, [Address],
STUFF(a.[Address], ss.search_idx, LEN(ss.search_val), ss.PostalServiceStandardSuffixAbbreviation) AS AddressAbbreviated
FROM [Address] a
OUTER APPLY (
SELECT TOP 1 *, LEN(a.[Address]) - LEN(txt.search_val) - NULLIF(CHARINDEX(REVERSE(txt.search_val), REVERSE(a.[Address])), 0) + 2 AS search_idx
FROM StreetSuffix ss
CROSS APPLY ( VALUES(ss.PrimaryStreetSuffixName), (ss.CommonlyUsedStreetSuffixOrAbbreviation) ) txt(search_val)
ORDER BY search_idx DESC, LEN(txt.search_val) DESC
) ss
ORDER BY a.Address_pk;
I tested it with an additional address '100 Avalon Av'.
Drew
I can think of at least four. Surprisingly, the one that I thought would perform the worst looks like it may actually perform the best.
SELECT a.Address_pk, a.[Address],
STUFF(a.[Address], ss.search_idx, LEN(ss.search_val), ss.PostalServiceStandardSuffixAbbreviation) AS AddressAbbreviated
FROM [Address] a
OUTER APPLY (
SELECT TOP (1) *, LEN(a.[Address]) - LEN(txt.search_val) - NULLIF(CHARINDEX(REVERSE(txt.search_val), REVERSE(a.[Address])), 0) + 2 AS search_idx
FROM (
SELECT ss.PrimaryStreetSuffixName, ss.CommonlyUsedStreetSuffixOrAbbreviation, ss.PostalServiceStandardSuffixAbbreviation
FROM StreetSuffix ss
UNION ALL
SELECT a.[Address], a.[Address], a.[Address]
) ss
CROSS APPLY ( VALUES (ss.PrimaryStreetSuffixName), (ss.CommonlyUsedStreetSuffixOrAbbreviation) ) txt(search_val)
ORDER BY search_idx DESC, LEN(txt.search_val) DESC
) ss
ORDER BY a.Address_pk;
I expected ISNULL or COALESCE to perform the best, and a CASE statement to perform next best. I only tested ISNULL, but here are the results.
UNION ALL
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 96 ms.
ISNULL
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 138 ms.
This certainly was a surprise to me. I'd be interested to see the results on a larger dataset.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA