SQL Server : find and replace part of a specific part of string that appears more than once in a string

Question

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.

Answer 1

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.