A deterministic string replaces and matches

Question

I have a constant computed column that calls the Scalar Valued Function . As you know, this function must be deterministic in order to preserve value. Even if the REPLACE function behaves in a deterministic way (I cannot think that it is not), SQL Server seems to interpret it as non-deterministic. Therefore, I cannot use it in a function.

I am trying to translate some non-English characters into English. Case sensitivity is important here. I wanted to convert the letters ğĞüÜşŞıİöÖçÇ to gGuUsSiIoOcC respectively. I can achieve this (in a "non-deterministic" way) simply by using something like:

 SET @ColumnValue = REPLACE(@ColumnValue COLLATE SQL_Latin1_General_CP1253_CS_AS, 'ı', 'i') --This character("ı") is converted to "?" after collation so that I manually replace it SET @ColumnValue = @ColumnValue COLLATE SQL_Latin1_General_CP1253_CS_AS --This line takes care of the other characters 

SQL Server interprets this code above as non-deterministic ( demo ) due to REPLACE and COLLATE (I think it is deterministic ..).

Another thing I tried was to use CHARINDEX with STUFF in the WHILE loop, but sorting has to be used because of the need for case sensitivity. Without matching, SQL Server sees this as deterministic, though.

What are my options?

Answer 1

Is your column really a varchar type, not an nvarchar ?

It looks like COLLATE SQL_Latin1_General_CP1253_CS_AS is deterministic for nvarchar but not deterministic for varchar .

The next function is deterministic. Note that you need N prefix string literals for it to work properly.

 CREATE FUNCTION dbo.TestFunc1 (@ColumnValue NVARCHAR(4000)) RETURNS NVARCHAR(4000) WITH SCHEMABINDING AS BEGIN SET @ColumnValue = REPLACE(@ColumnValue COLLATE SQL_Latin1_General_CP1253_CS_AS, N'ı', N'i') --This character("ı") is converted to "?" after collation so that I manually replace it SET @ColumnValue = @ColumnValue COLLATE SQL_Latin1_General_CP1253_CS_AS --This line takes care of the other characters RETURN @ColumnValue END 

---

If you need to use varchar , I would use binary sorting to replace specific characters. The following function is also deterministic.

 CREATE FUNCTION dbo.TestFunc2 (@ColumnValue VARCHAR(8000)) RETURNS VARCHAR(8000) WITH SCHEMABINDING AS BEGIN SET @ColumnValue = REPLACE(@ColumnValue COLLATE Latin1_General_BIN2, N'ı', N'i') SET @ColumnValue = REPLACE(@ColumnValue COLLATE Latin1_General_BIN2, N'ö', N'o') ... RETURN @ColumnValue END