2

I am a small business owner who self-taught SQL Server to manage mailing data. This resource has helped me so many times in the past looking at other folks questions, however, this is the first time asking a question. Using SQL Server 2017.

In the address column I want to change abbreviations to spelled out words. For example, I want to change 'St' to 'Street'. I know how to do a basic find and replace in T-SQL, but what I am running into is that if 'St' is in the name of the actual street name, it is getting changed as well. For example, '123 Stripe St' becomes '123 Streetripe Street'. My desired output would be '123 Stripe Street'.

I am using the following

UPDATE Person
SET Addr = REPLACE(Addr, 'St', 'Street')
WHERE Addr like '%St'

Can someone please help me to replace just the abbreviated part of the address?

Any help is very much appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matt
  • 23
  • 2

2 Answers2

2

Normalizing addresses can be a slippery slope. You may be surprised on the variations of Blvd. This is why I suggest you take a peek at Address standardization within a database

That mentioned, here is a simple and easy to expand option

Example

Declare @YourTable table (id int,Addr varchar(150))
Insert Into @YourTable values
 (1,'123 Stripe St')
,(2,'555 SW Main St, Providence, RI')


Update @YourTable
 Set Addr = ltrim(rtrim(
            replace(
            replace(' '+Addr+' ',' St ',' Street ')
            ,' St, ',' Street, ')
            ))

Select * from @YourTable

Returns

id  Addr
1   123 Stripe Street
2   555 SW Main Street, Providence, RI  -- Notice middle St with a comma.
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Thank you for your help, I will give this a try. The good news is that all of my address go through a product from the USPS called CASS certification which makes all the iterations of boulevard, Blvd. It will standardize every address to the official USPS version of the address. – Matt Jan 05 '19 at 16:54
  • @Matt Great option. Funny, I built an app for the USPS. I didn't know they had a public API for validation. – John Cappelletti Jan 05 '19 at 17:16
  • 2
    This worked great. It also picked up the addresses that had a trailing Apt #s. Thanks again! For address standardization, like most thing government, you don't get the product straight from the USPS, you go through a third party vendor. I use BCC. Lorton Data, and Melissa data are also very popular providers. I pay a subscription and with standardization, also get NCOA (national change of address). – Matt Jan 05 '19 at 20:21
1

For this particular example, you can do:

UPDATE Person
    SET Addr = LEFT(Addr, LEN(Addr) - 2) + 'Street'
    WHERE Addr like '% St';

However, for more general patterns in the middle of the string, this will get more challenging. SQL Server is not the optimal tool for this type of string manipulation.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you very much for your response. Before I posted my question, I was trying to use the RIGHT command, but kept getting an error. It didn't occur to me to use LEN. Thanks again. – Matt Jan 05 '19 at 16:57