1

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?

Fred
  • 3,365
  • 4
  • 36
  • 57

2 Answers2

5

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;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • 1
    You should [mention that](https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=5dfa32a9b13ea6c2e89b2dce1f146ed5) Larnu – Ilyes Jun 13 '20 at 19:38
  • Depends on the [version](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5dfa32a9b13ea6c2e89b2dce1f146ed5) @Sami. As they haven't mentioned one I assumed they have the latest, but the workaround is to use a sub query. – Thom A Jun 13 '20 at 19:53
  • Documentations for Replace function: https://learn.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql?view=sql-server-ver15 – Vahid Farahmandian Jun 16 '20 at 10:01
-1

We can use REPLICATE too, instead of writing collation at high level, just need concatenation of * after after the N'foo' value right?

SELECT CONCAT(N'foo',REPLICATE('*',1600))
Thirmal Sangisetti
  • 1