'SQL Performance (Replace)

I have a table that has about 400,000+ rows. I am writing some pattern matching code but need to clean up a column before I do so. This boils down to doing a replace-like operation.

I tried listing them all out one at a time...

 Update T_ADDRESS set ADDR_LINEONE = REPLACE(ADDR_LINEONE,' southeast ',' se ')
 Update T_ADDRESS set ADDR_LINEONE = REPLACE(ADDR_LINEONE,' southwest ',' sw ')

Since I have over 500 of these...it took too long.

Now I am trying to nest them...

 Update T_ADDRESS set ADDR_LINEONE = REPLACE(REPLACE(ADDR_LINEONE,' southwest ',' sw '),' southeast ',' se ')

But this is still painfully slow. I need to make this code work on tables of all sizes (1 record to 5 million records).

Anyone have any advice? I am using SQL Server by the way.

sqldatabasesql-server-2005replace


Solution 1:[1]

Write a CLR procedure. TSQL is not great at (or designed for) handling large numbers of string manipulations.

Regular Expressions Make Pattern Matching And Data Extraction Easier

Solution 2:[2]

Create #TEMP table with new values then inner join Something like this

create table #TempValues
(oldAres varchar(12),newadres varchar(2))

insert into #TempValues
select 'southeast','se'
union all 
select 'southwest','sw'

update T_ADDRESS
set addr_lineone=t.newadres
from T_ADDRESS inner join #TempValues t on T_ADDRESS.addr_lineone=t.oldAdres

Solution 3:[3]

You could write the update statement to use a CASE statement in place of the multiple REPLACEs although I've no idea whether this will execute any faster for you.

Solution 4:[4]

Old question, but still useful. Your query updates all the columns of the table, with a large penalty if this column is indexed. You can improve the speed by filtering the fields that do not need to be updated as follows:

Update T set ADDR_LINEONE = REPLACE(ADDR_LINEONE,' southeast ',' se ') FROM T_ADDRESS T WHERE ADDR_LINEONE LIKE '% southeast %'
Update T set ADDR_LINEONE = REPLACE(ADDR_LINEONE,' southwest ',' sw ') FROM T_ADDRESS T WHERE ADDR_LINEONE LIKE '% southwest %'

Unless all your addresses contain southeast or southwest, this will be an order of magnitude faster!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mitch Wheat
Solution 2 adopilot
Solution 3 Chris W
Solution 4 jcmeyrignac

Related Questions