CODEWITHSUNDEEP
sqlsql-server
DixieFlatline
DixieFlatline

Reputation: 484

SQL Server - Most Efficient Way to Replace Multiple String Patterns

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?

Upvotes: 0

Views: 355

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

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.

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31785

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

Upvotes: 1

Related Questions