Dynamically select all address rows in table with "street", and replace with "st"

Question

How do I do a replace that will select all address rows with "street", and replace with "st". I know all rows don't contain "street", so it should be best to select them first, right? I usually don't modify tables at this job, and want to make sure I get it right.

So far I saw sql replace, but it doesn't do a select of the rows/columns with "street" in them first. This one does a select/replace replace select.

My data is about 4500 rows long. Some are "street"=>st, "avenue"=>ave, "st."=>st, etc.

Can I just do a

SELECT address_1 as addr
REPLACE(addr, 'Street', 'St')
FROM table

Update: How do I keep it from changing 'Streets' to 'Sts'? Some addresses show two cross Streets and I'd like to keep them as Streets in that case.

Thanks!

Don't change the parameters of your question after someone has answered based on the original question. That's confusing and not fair to the effort someone has put into the original question. If you realize, after an answer has helped with the original question, that another problem has cropped up, then ask a new question. — Cindy Meister, May 15 '19 at 18:03
I didn't change it after the guy answered. His answer is usable with some changes for the Streets/Sts thing too. — Michele, May 16 '19 at 11:58

score 1 · Accepted Answer · answered May 15 '19 at 17:42

1

Try this-

UPDATE your_table
SET address = REPLACE(address,'street','st')
WHERE Address like '%street%'

answered May 15 '19 at 17:42

mkRabbani

16,295
2
15
24

So I wouldn't need to select the address column first? – Michele May 15 '19 at 17:46
1

If you want to edit your value in database column, select is unnecessary. – mkRabbani May 15 '19 at 17:47

Dynamically select all address rows in table with "street", and replace with "st"

1 Answers1