Simple Replace in SQL

Question

I'm trying to do an Update query to correct street address abbreviations. I want to correct anything that is like 123 Main Cr to be 123 Main Cir. However, I don't want it to mess up addresses like 123 Creek Cir, and optionally I would like it to catch 123 Main Cr Apt 28. This is what I have so far. This is using SQL Server 2008.

UPDATE AddressTable
SET AD = replace(AD, 'Cr', 'Cir')
WHERE right(ad,3) like '% cr%'

Thank you in advance.

Could you please tag your question with the name of the database you're using? — abhi, Apr 09 '14 at 19:02
Addresses can be really complicated without a verification/standardization service. You might need more than just a magic regex. — Matt, Apr 09 '14 at 19:30

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

Try this, and update your UPDATE statement accordingly. I believe what you're asking is that you want to make sure you replace with "word boundary":

Referenced:

Oracle REGEXP_LIKE and word boundaries

Test1:

SELECT REGEXP_REPLACE ( '123 Creek Cr' , '(^|\s)Cr(\s|$)' , ' Cir' ) AS output_txt
FROM dual;

Test2:

SELECT REGEXP_REPLACE ( '123 Main Cr' , '(^|\s)Cr(\s|$)' , ' Cir' ) AS output_txt
FROM dual;

edited May 23 '17 at 11:51

Community

1
1

answered Apr 09 '14 at 19:03

Roberto Navarro

948
4
16

I hope he's using Oracle – SQLMason Apr 09 '14 at 19:19
Nope, not using Oracle. MS SQL 2008. – user3513237 Apr 09 '14 at 20:01

score 0 · Answer 2 · answered Apr 09 '14 at 20:04

It sounds like you're looking to standardize addresses, with correct abbreviation, spelling, etc. Parsing and standardizing addresses is a very complex issue. There are, of course, many factors that go into how the address is formed, and RegEx can't handle all address parsing because addresses aren't regular. For example, if you had this address:

100 Main St Helena CA

did the person mean

100 Main Street, Helena, CA?

Or is it

100 Main [street], St. Helena, CA?

This is obviously a rather simple example, but you can see how a SQL function can have a difficult time parsing out what the user intended with the address input. Figuring out all the factors, variations, and regulations that go into standardizing addresses can be a monumental task. So I suggest that you look into using a service that specializes in address standardization. Utilizing such a service can make your task significantly easier.

Companies such as SmartyStreets have gone through all the headache and struggle of figuring out how addresses are formatted, and will return standardized address output for you to update your database with. SmartyStreets specializes in United States postal addresses, but there are other companies that do international addresses as well. Here are some companies that do address standardization:

These and many other services can handle the task of cleaning up your address data much more quickly than it would take for you to account for all the factors and variations involved in standardizing addresses.

Thank you. I understand the complexities of addresses and see the use of a service like this. However, in this case I am simply wanting to change all abbreviations Cr to Cir. I can do it with "find and replace" in MS Access, but just wanted to know how to do it in MS SQL. — user3513237, Apr 09 '14 at 20:32
Depending on the size of your list, you can often have a service provider (like SmartyStreets) do it for free or for less than $10 in about three minutes. Then you won't have any guesswork or high probably of data corruption. — Jonathan Oliver, Apr 10 '14 at 02:06

Joseph B · Answer 3 · 2014-04-09T23:46:18.897

0

The following query will probably work for you:

UPDATE AddressTable
SET AD = 
    (CASE SUBSTRING(AD, LEN(AD)-1, 2)
      WHEN 'Cr' THEN replace(LEFT(AD, LEN(AD)-2) + 'Cir', ' Cr ', ' Cir ')
      ELSE replace(AD, ' Cr ', ' Cir ')
    END)
WHERE AD like '% Cr'
OR    AD like '% Cr %'

edited Apr 09 '14 at 23:46

answered Apr 09 '14 at 21:28

Joseph B

5,519
1
15
19

This didn't work. It turned "123 Crows Cr" into "123 Cirows Cir". – user3513237 Apr 09 '14 at 22:44
Thank you for the feedback @user3513237. I have modified the query now. Please verify. – Joseph B Apr 09 '14 at 23:38

score 0 · Answer 4 · answered Apr 10 '14 at 00:57

0

You could append a space to the string and filter the result like this:

WHERE ad + ' ' LIKE '% Cr %'

Similarly, you could append a space to the string and replace all occurrences of ' Cr ' with ' Cir ':

REPLACE(ad + ' ', ' Cr ', ' Cir ')

and them just trim the trailing space. So, the end result would look like this:

UPDATE AddressTable
SET AD = RTRIM(REPLACE(AD + ' ', ' Cr ', ' Cir '))
WHERE AD + ' ' LIKE '% Cr %'
;

answered Apr 10 '14 at 00:57

Andriy M

76,112
17
94
154

Naturally, RTRIM will also remove the trailing spaces coming from the original value. It is possible to keep them if necessary, although the resulting expression would become more complicated. I hope that is not necessary. – Andriy M Apr 10 '14 at 01:00

Simple Replace in SQL

4 Answers4