CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012sql-server-2014
Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

SQL Server replace function bug?

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?

Upvotes: 0

Views: 310

Answers (2)

Guffa
Guffa

Reputation: 700800

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-.

Upvotes: 3

James Z
James Z

Reputation: 12317

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....

Upvotes: 6

Related Questions