Deterministic string replace and collate

Question

I have a persisted computed column which calls a Scalar Valued Function. As you know, this function needs to be deterministic in order to persist the value. Even if REPLACE function behaves in a deterministic way(I can't think a case where it's not), SQL Server seems to interpret it as nondeterministic. Therefore, I can not use it in the function.

What I try to do is converting some non-english characters to english. Case sensitivity is important here. I wanted to convert the letters of ğĞüÜşŞıİöÖçÇ to gGuUsSiIoOcC respectively. I can achieve it(in a "nondeterministic" 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 nondeterministic (demo) because of REPLACE and COLLATE (I think that it's deterministic though..).

Another thing that I tried was using CHARINDEX with STUFF in a WHILE loop but needed to use collation because of the need of case sensitivity. Without the collation, SQL Server treats it as deterministic though.

What are my options?

Answer 1

Is your column really of varchar type, not nvarchar?

It looks like COLLATE SQL_Latin1_General_CP1253_CS_AS is deterministic for nvarchar, but non-deterministic for varchar.

The following function is deterministic. Note, that you need to prefix your string literals with N for it to work correctly.

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, then I'd use binary collation 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