October 20, 2016 at 3:05 pm
I'm working on creating a unique address list, but many of the duplicates come in with Street one time and ST the next. I have a street suffix table with the common suffix name and the abbreviation, along with common abbreviations that people use that are not the standard one.
I'd like a select statement that grabs the first match for the last suffix in a list, so an address like 1400 Court Place only replaces Place with PL. A match on the commonly used abbreviation should also trigger a replacement. So far the closest I've come is a CROSS JOIN, but that gave me matches that are not useful. Here is the example data:
DROP TABLE [dbo].[StreetSuffix]
CREATE TABLE [dbo].[StreetSuffix]
(
[StreetSuffix_pk] INT NOT NULL CONSTRAINT PK_StreetSuffix PRIMARY KEY,
[PrimaryStreetSuffixName] VARCHAR(10) NOT NULL CONSTRAINT DF_StreetSuffix_PrimaryStreetSuffixName DEFAULT (''),
[CommonlyUsedStreetSuffixOrAbbreviation]VARCHAR(10) NOT NULL CONSTRAINT DF_StreetSuffix_CommonlyUsedStreetSuffixOrAbbreviation DEFAULT (''),
[PostalServiceStandardSuffixAbbreviation]VARCHAR(10) NOT NULL CONSTRAINT DF_StreetSuffix_PostalServiceStandardSuffixAbbreviation DEFAULT ('')
)
TRUNCATE TABLE [dbo].[StreetSuffix];
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (1, 'ANEX', 'ANX', 'ANX');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (2, 'COURT', 'COURT', 'CT');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (3, 'PLACE', 'PL', 'PL');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (4, 'AVENUE', 'AV', 'AVE');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (5, 'AVENUE', 'AVE', 'AVE');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (6, 'COURT', 'COURT', 'CT');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (7, 'COURT', 'CT', 'CT');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (8, 'STREET', 'STREET', 'ST');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (9, 'STREET', 'STRT', 'ST');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (10, 'TRACE', 'TRACE', 'TRCE');
SELECT * FROM [dbo].[StreetSuffix];
DROP TABLE [dbo].[Address]
CREATE TABLE [dbo].[Address]
(
[Address_pk] INT IDENTITY(1,1)NOT NULL CONSTRAINT PK_Address PRIMARY KEY,
[Address]VARCHAR(150)NOT NULL CONSTRAINT DF_Address_Address DEFAULT (''),
)
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1756 2nd Avenue');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('220 East 41st Street');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('777 6th Avenue');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1466 2nd Avenue');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('557 10th AV');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('745 7th Avenue');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1200 India Street');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('125 3rd STRT');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1201 G Street NW');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1400 Court Place');
SELECT [Address_pk], [Address],
REPLACE([Address],ss.[PrimaryStreetSuffixName],ss.[PostalServiceStandardSuffixAbbreviation]) AS [AddressAbbreviated]
FROM [dbo].[Address]
CROSS APPLY [dbo].[StreetSuffix] ss
ORDER BY [Address_pk];
SELECT [Address_pk], [Address],
REPLACE([Address],ss.[PrimaryStreetSuffixName],ss.[PostalServiceStandardSuffixAbbreviation]) AS [AddressAbbreviated]
FROM [dbo].[Address]
CROSS JOIN [dbo].[StreetSuffix] ss
ORDER BY [Address_pk];
October 20, 2016 at 3:32 pm
I guess, a simple INNER JOIN would be too trivial for you. 🙂
SELECT [Address_pk], [Address],
REPLACE([Address],ss.[PrimaryStreetSuffixName],ss.[PostalServiceStandardSuffixAbbreviation]) AS [AddressAbbreviated]
FROM [dbo].[Address] a
INNER JOIN [dbo].[StreetSuffix] ss on a.[Address] LIKE '% ' + ss.PrimaryStreetSuffixName + '%'
UNION
SELECT [Address_pk], [Address],
REPLACE([Address],ss.[CommonlyUsedStreetSuffixOrAbbreviation], ss.[PostalServiceStandardSuffixAbbreviation]) AS [AddressAbbreviated]
FROM [dbo].[Address] a
INNER JOIN [dbo].[StreetSuffix] ss on a.[Address] LIKE '% ' + ss.CommonlyUsedStreetSuffixOrAbbreviation+ '%' and not (a.[Address] LIKE '% ' + ss.PrimaryStreetSuffixName + '%')
UNION
SELECT [Address_pk], [Address], 'N/A'
FROM [dbo].[Address] a
WHERE NOT EXISTS (
SELECT *
FROM [dbo].[StreetSuffix] ss WHERE a.[Address] LIKE '% ' + ss.CommonlyUsedStreetSuffixOrAbbreviation+ '%'
OR (a.[Address] LIKE '% ' + ss.PrimaryStreetSuffixName + '%')
)
ORDER BY [Address_pk]
;
_____________
Code for TallyGenerator
October 20, 2016 at 3:42 pm
Sometimes I just can't see the solutions even when it's right in front of me. Never thought of using a union. or a compound operator.
One question is the output displays row 10 twice,
Address_pkAddressAddressAbbreviated
101400 Court Place1400 Court PL
101400 Court Place1400 CT Place
I only want the first one. I'm hoping you have an equally slick way to remove the 2nd result from this listing!
October 20, 2016 at 4:21 pm
This works with the sample data and doesn't produce the second record for Court Place.
SELECT Address_pk, [Address],
STUFF(a.[Address], ss.search_idx, LEN(ss.search_val), ss.PostalServiceStandardSuffixAbbreviation)
FROM [Address] a
OUTER APPLY (
SELECT TOP 1 *, CHARINDEX(txt.search_val, a.[Address]) 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 Address_pk;
I also have another solution, but it doesn't seem to perform as well. Neither one will be stellar, because the condition isn't SARGable.
I also used STUFF instead of REPLACE, because REPLACE will update ALL occurrences of the string, whereas STUFF will only do one occurrence in case there are multiple occurrences of the search string in the address (although it does need further refining).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 20, 2016 at 4:38 pm
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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 20, 2016 at 6:18 pm
jeff.born (10/20/2016)
Sometimes I just can't see the solutions even when it's right in front of me. Never thought of using a union. or a compound operator.One question is the output displays row 10 twice,
Address_pkAddressAddressAbbreviated
101400 Court Place1400 Court PL
101400 Court Place1400 CT Place
I only want the first one. I'm hoping you have an equally slick way to remove the 2nd result from this listing!
One of these output records must be wrong.
I guess it's the 2nd one.
is there any rule defined for such cases?
_____________
Code for TallyGenerator
October 21, 2016 at 11:58 am
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
October 21, 2016 at 12:04 pm
Sergiy, The rule is that only the last token should be replaced. So
100 Lake Drive should be 100 Lake DR
100 Lake Shore Drive Road should be 100 Lake Shore Drive RD
It gets tricky with post directionals however, so
100 Lake Drive SW should be 100 Lake DR SW. If there is a directional then the second to last token is replaced.
Thanks for the ideas, help and forcing me to think this through further too!
Sergiy (10/20/2016)
jeff.born (10/20/2016)
Sometimes I just can't see the solutions even when it's right in front of me. Never thought of using a union. or a compound operator.One question is the output displays row 10 twice,
Address_pkAddressAddressAbbreviated
101400 Court Place1400 Court PL
101400 Court Place1400 CT Place
I only want the first one. I'm hoping you have an equally slick way to remove the 2nd result from this listing!
One of these output records must be wrong.
I guess it's the 2nd one.
is there any rule defined for such cases?
October 21, 2016 at 12:34 pm
Is there a fixed set of possible directionals?
_____________
Code for TallyGenerator
October 21, 2016 at 1:06 pm
Yes there are, they are:
N, S, E, W, NE, NW, SE, SW
Sergiy (10/21/2016)
Is there a fixed set of possible directionals?
October 21, 2016 at 2:30 pm
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
October 21, 2016 at 2:59 pm
i just recently did the opposite of what the OP is looking to do: i expanded all abbreviations to full words;
mine was an investigation to eliminate variations in abbreviations and address formatting in order to find duplicates (because the powers that be nixed the "run it through a CASS company")
i found the order of the REPLACE/STUFF commands was really important.
for example I was cleaning up " 55 N St. Andrews St. blvd" to be "55 N Saint Andrews St. blvd" first, to finally get to "55 North Saint Andrews Street Boulevard"
I ran my long logic through the Sakila database from MySQL(imported into SQL server), the Ashley Madison database, Adventureworks2014 and a real database of addresses from my company;
all in all hundreds of thousands of addresses.
I thought it might make for a good article; at least to make people either say they liked it scream "no, don't do it that way!"
Lowell
October 21, 2016 at 3:53 pm
Drew,
I'm not sure how to write the ISNULL version.. This syntactically incorrect version I came up with is:
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 (ISNULL((SELECT ss.PrimaryStreetSuffixName, ss.CommonlyUsedStreetSuffixOrAbbreviation, ss.PostalServiceStandardSuffixAbbreviation
FROM StreetSuffix ss),
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;
doesn't compile.
The Union ALL option scales well with 200,000+ addresses to parse, and with almost everything looking great a few addresses seem to cause troubles. I had to add some street suffixes to illistrate along with additional addresses. But the end result is Boulevard comes out BoulevaRD and Avenue East shows up as Avenue EaST (West has the same issue)
The issue I haven't been able to reproduce has the output with DRIVe instead of DR, but all the examples I provided that didn't work in a bigger sample of suffixes and addresses work in the smaller sample.
Before I drop in the next samples, I wanted to say thanks and I'll mark this the solution, since it will address 99% of the duplication issues and the rest we can deal with. It would be nice to see a solution that will work for everything thought, or 99.9%!
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (1, 'ANEX', 'ANX', 'ANX');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (2, 'COURT', 'COURT', 'CT');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (3, 'PLACE', 'PL', 'PL');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (4, 'AVENUE', 'AV', 'AVE');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (5, 'AVENUE', 'AVE', 'AVE');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (6, 'COURT', 'COURT', 'CT');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (7, 'COURT', 'CT', 'CT');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (8, 'STREET', 'STREET', 'ST');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (9, 'STREET', 'STRT', 'ST');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (10, 'TRACE', 'TRACE', 'TRCE');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (11, 'ROAD', 'RD', 'RD');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (12, 'ROAD', 'ROAD', 'RD');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (13, 'ROADS', 'ROADS', 'RDS');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (14, 'ROADS', 'RDS', 'RDS');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (15, 'STREET', 'ST', 'ST');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (16, 'SQUARE', 'SQ', 'SQ');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (17, 'SQUARE', 'SQR', 'SQ');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (18, 'SQUARE', 'SQRE', 'SQ');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (19, 'SQUARE', 'SQU', 'SQ');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (20, 'SQUARE', 'SQUARE', 'SQ');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (21, 'SQUARES', 'SQRS', 'SQS');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (22, 'SQUARES', 'SQUARES', 'SQS');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (23, 'DRIVE', 'DR', 'DR');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (24, 'DRIVE', 'DRIV', 'DR');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (25, 'DRIVE', 'DRIVE', 'DR');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (26, 'DRIVE', 'DRV', 'DR');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (27, 'DRIVES', 'DRIVES', 'DRS');
INSERT INTO [dbo].[StreetSuffix] (StreetSuffix_pk, PrimaryStreetSuffixName, CommonlyUsedStreetSuffixOrAbbreviation, PostalServiceStandardSuffixAbbreviation) VALUES (28, 'BOULEVARD', 'BLVD', 'BLVD');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1756 2nd Avenue');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('220 East 41st Street');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('777 6th Avenue');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1466 2nd Avenue');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('557 10th AV');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('745 7th Avenue');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1200 India Street');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('125 3rd STRT');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1201 G Street NW');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1400 Court Place');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1200 India ST');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1200 India Road');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('8326-8336 Halls Ferry Road');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('8336 Halls Ferry Road');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('8326-8336 Ferry Road');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('8326-8336 Halls Road');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('8336 Ferry Road');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('8336 Halls Road');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1 Bowdoin Square');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('48 Martin Luther King');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('2 Penn Plaza East');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('17 FDR Drive');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('130 Co-op City Boulevard');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1025 Richard Arrington');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1127 West Broadway');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1125-1127 229th Drive North');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('1 Gateway Center');
INSERT INTO [dbo].[Address] ([Address]) VALUES ('100 Van Cortlandt Avenue West');
October 21, 2016 at 5:34 pm
jeff.born (10/21/2016)
Drew,I'm not sure how to write the ISNULL version.. This syntactically incorrect version I came up with is:
The ISNULL replaces the result of the STUFF with the original address.
You might be able to play around with PATINDEX instead of CHARINDEX and include a space as part of your pattern.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 21, 2016 at 9:28 pm
@jeff.Born,
The list you have looks a bit familiar. Are you, by any chance, using the tables from the USPS? I ask because I went through this very same thing just a couple of months ago and may have a nasty fast solution but first want to compare my solution with some of the others posted here. I just need to know if you're using the USPS tables for this type of stuff to do my tests with because those are what I used.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply