SQL Replace Issues

Question

Good Day All -

Trying to use the replace function to clean up some address issues during data extract.

The issue I'm running into is I only want to update the address if the street type is abbreviated.

Address 1 needs to be updated so 'Ci' becomes 'Circle', but I don't want address 2 to end up with 'Circlercle'.

187624 W Windor Ci
187624 W Windor Circle

select replace(address, ' Ci', ' Circle')  as address

At times the 'Ci' value are the last characters in the field or they can be followed by and apartment number. I tried using ' Ci ' for the find, but still missing addresses if the address field ends with ' Ci'.

Any thoughts much appreciated.

Tag your question with the database you are using. – Gordon Linoff Oct 02 '20 at 16:55 — Gordon Linoff, Oct 02 '20 at 16:55

Radagast · Accepted Answer · 2020-10-02T17:14:31.703

0

Use a case expression to make replace apply to only relevant records

select case when address like '% Ci%' and address not like '% Circle%' 
            then replace(address, ' Ci', ' Circle')
            else address end  as address

You can also use a nested replace assuming resulting "Circlercle" is not a legitimate address suffix

select replace(replace(address, ' Ci', ' Circle'),'Circlercle','Circle') as address

edited Oct 02 '20 at 17:14

answered Oct 02 '20 at 16:59

Radagast

5,102
3
12
27

Thank you for the response. I did consider the second example you proveded, but figured it could get a bit cumbersom as I'm going to have to replace multiple street types. Is there a limit on nested replace statements? – Chelle Oct 02 '20 at 17:27
@Chelle It might depend on the db, but I want to say 32 for SQL Server. If you have a few more street types to consider, you'd be better off with the case expression. Just add additional `when` to additional street types. If it's way too many, you could build a lookup table with a few columns that specify what needs to be matched, what doesn't, and what the replacement should be. Then you just join to that table and use only one replace – Radagast Oct 02 '20 at 17:39

score 0 · Answer 2 · answered Oct 02 '20 at 18:10

Normalizing an address is trickier that one may think. Perhaps take a peek at Address standardization within a database

If open to a UDF, you can perform MULTPLE replaces as defined by a table. The following example can be converted into a function and I would suggest the table variable be an actual table.

Example

Declare @S varchar(max) = '123 NW Bvd.'

Declare @Map table (strFrom varchar(50),strTo varchar(100))
Insert Into @Map values
 ('NW'   ,'Northwest')
,('Blvd' ,'Boulevard')
,('Bvd'  ,'Boulevard')
,('Bvd.' ,'Boulevard')
-- Add as many as needed

Set @S=' '+@S+' '

Select @S=replace(@S,' '+strFrom+' ',' '+strTo+' ')
 From  (Select top 1000 * 
         From  @Map 
         Order By len(strFrom) desc
       ) A
 Order By len(strFrom) desc

Returns

123 Northwest Boulevard

Thank you for your response John! I was able to use the replace function to get what I needed, but this seems more elegant. Going to try to employ in my script and if anything at least I'll get some practice :) — Chelle, Oct 02 '20 at 22:52
@Chelle Always happy to help. To be perfectly honest, this approach is far more robust than having to maintain code. — John Cappelletti, Oct 02 '20 at 23:28

SQL Replace Issues

2 Answers2