How SQL Server Replace Works
Can anyone explain why the function Replace
doesn't replace all occurrences string_pattern
with string_replacement
?
select REPLACE(' A A A A ',' A ',' B ')
Output: B A B A
Expected Result: B B B B
select REPLACE('.A.A.A.A.','.A.','.B.')
Output: .B.A.B.A.
Expected Result: .B.B.B.B.
In the above query, why are only alternative values being replaced?
I know the above problem can be fixed by adding another replacement .
select replace(REPLACE('.A.A.A.A.A.A.A.A.A.','.A.','.B.'),'.A.','.B.')
But why Replace
doesn't single replace all occurrences string_pattern
withstring_replacement
Can anyone explain what is going on?
source to share
The first place (or period) after the first A
is considered part of the first match. Example:
.A.A.A.A.
111 222
1
is the first match, a 2
is the second match. After completing the first replacement, the function looks at the second A
and does not backup. A.A
doesn't match, so he moves forward and looks at .A.
.
In another way, you are trying to match overlapping lines. It looks like trying to replace bob
with job
this:
bobob
You expect to see jojob
, but that's not how it works. After the first replacement, you have jobob
, but the replace function has already moved on to the second o
. He doesn't see bob
s anymore , so he stops.
source to share