SQL Server - Most Efficient Way to Replace Multiple String Patterns

Question

I need to clean up a string field and replace a dozen different patterns. I know you can use a nested REPLACE function, but doing that a lot is a real pain to type and makes for some very ugly code.

Is there a more efficient way to do this?

The nested replace() is possibly the fastest approach. There are other methods, but they are probably slower for the general case. — Gordon Linoff
– Gordon Linoff, Commented Jan 27, 2016 at 20:40

Gordon Linoff · Accepted Answer · 2016-01-27 20:43:40Z

3

You can do the nested replace . . . without nesting. Here is one method:

select t.*, t_r12.col_r
from t outer apply
     (select replace(t.col, 'in1', 'out1') as col_r) t_r01 outer apply
     (select replace(t_r01.col_r, 'in2', 'out2') as col_r) t_r02 outer apply
     (select replace(t_r02.col_r, 'in3', 'out3') as col_r) t_r03 outer apply
     . . .;

In this context, I don't think that outer apply adds any (significant) overhead. However, this does require naming all the intermediate results.

answered Jan 27, 2016 at 20:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tab Alleman · Accepted Answer · 2016-01-27 20:40:56Z

1

You could write a CLR procedure that uses a REGEX to do the replace.

answered Jan 27, 2016 at 20:40

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Collectives™ on Stack Overflow

SQL Server - Most Efficient Way to Replace Multiple String Patterns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related