Replace of NCHAR(1600) is not working on SQL Server

Question

When I run bellow code the NCHAR(1600) is not replaced

SELECT REPLACE(N'foo' + NCHAR(1600), NCHAR(1600), '**') --> output: fooـ

• My database collation is Persian_100_CI_AI
• The default collation of SQL server is Persian_100_CI_AI too
• My SQL server version is Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64)

Does anyone know what is the problem?

Answer 1

Some characters aren't matched well in certain collations. As a result some will be matched against others when they aren't the same, and others may end up not being matched even though they are.

REPLACE uses collations and so it appears that the matching of the same character isn't working. In such cases, it's often therefore an idea to use a different collation inside the REPLACE, and if you need to return to the original collation, use COLLATE again outside:

SELECT REPLACE(N'foo' + NCHAR(1600) COLLATE Persian_100_BIN, NCHAR(1600), '**') COLLATE Persian_100_CI_AI;