Am I missing something in the the following statement?
select replace('xAxAxAx', 'xAx', 'xBx')
Because it results in:
xBxAxBx
Is this known bug or I have missed something basic here?
Microsoft says that replace function:
Replaces all occurrences of a specified string value with another string value.
Isn't second xAx an occurrence here?
That's jut matter of definition what is an occurrence and how do you deal with overlapping instances. That's exactly how I would expect it to work, since handling the data one occurrence at the time means that 'xAxAxAx' is xAx + A + xAx -- and whatever is the result of the first replaced isn't being considered as a new occurrence, therefore those 2 get replaced.
Similarly the result of replace ('xx', 'x', 'xx') is xxxx, not xxxxxxxxxx....
No, the second xAx is not an occurance. An occurance can not overlap another occurance, so there are only two occurances of xAx in the string:
'xAx' + 'A' + 'xAx'
Consider if you did a replacement like this, which makes it a bit clearer:
replace('xAxAxAx', 'xAx', '-B-')
If the second xAx would count as an occurance, after replacing the first occurance, would the string be -B-xAxAx? Would the x that is shared between the first xAx and the second be duplicated so that it could be replaced in both occurances? Still, that would make the result -B--B--B- rather than -B-B-B-.
replace('xAxAxAx', 'A', 'B'), but I was expecting replace behaving withreplace('xAxAxAx', 'xAx', 'xBx')result inxBxBxBx