Why replace('aaaaaaa', 'a', 'b

问题:

I am having a problem with the select replace('aaaaaaa', 'a', 'b') in T-SQL (SQL Server 2012). I have not tried with other versions of SQL.

I epxect to get 'bbbbbbb' but the result is 'aaaaaab'.

When I try to test more then I have found that replace does not replace with 'aa' (double 'a') but work with single 'a'.

Example: - If I execute replace('aa1a11a11a', 'a', 'b') then the result is 'aa1b11b11b' (it does not replace 'aa')

Does anybody know why is it? Is it a bug or a design feature from T-SQL?

Edited 2017.09.28: - Thank you all. I am clear the case now with the answer in details of Sepupic.

回答1:

Replace does works as you expected:

You have another problem, maybe not all your 'a' characters are 'a'? Maybe they are other symbols, for example, cyrillic 'a'?

.........................................................

I was able to reproduce it after reading comments about Danish collation and 'aa'. So Richard was right, and here is the repro:

declare @t table (col varchar(100) collate Danish_Norwegian_CI_AI);
insert @t values ('aaaaaa')
select replace(col, 'a', 'b')
from @t;

And here is how to fix this issue:

declare @t table (col varchar(100) collate Danish_Norwegian_CI_AI);
insert @t values ('aaaaaa')
select replace(col collate Danish_Norwegian_BIN2, 'a', 'b')
from @t;

回答2:

As mentioned in the comments, I was able to reproduce on SQL Server 2016.

@sepupic, as per your request:

UPDATE Unfortunately, it still doesn't work. I'm genuinely not pulling your leg here!