sql-servert-sqlsql-server-2017sql-server-collation

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


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

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

Does anyone know what is the problem?


Solution

  • 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;