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?

@MartinSmith Thanks for the demo link and great edit. In the demo, the nonenglish characters are automatically converted to english characters though (even when directly returning the parameter). However, one should assume it's not.. — yakya, Jun 21 '17 at 11:52
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`. — Vladimir Baranov, Jun 21 '17 at 12:11
So the fix might be quite simple then. http://rextester.com/DHLVJJ33503 — Martin Smith, Jun 21 '17 at 12:17

Vladimir Baranov · Accepted Answer · 2017-06-21T13:53:27.747

6

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

edited Jun 21 '17 at 13:53

answered Jun 21 '17 at 12:24

Vladimir Baranov

31,799
5
53
90

`Collate`'s behavior was different in `varchar` and `nvarchar`. That second `SET` didn't work. It didn't convert the characters at all. However, `REPLACE` also became deterministic after using `nvarchar` and I took advantage of that. I ended up writing `REPLACE` for each character I want to convert (Just like in the first `SET`). – yakya Jun 21 '17 at 13:23
@sotn, yes, if you have a list of specific characters to replace, just use `REPLACE` for each character. With binary collation it will be deterministic with `varchar` as well. – Vladimir Baranov Jun 21 '17 at 13:55
In the demo site, yes. But I tried it in my local and it was also non-deterministic using Latin1_General_BIN2 with varchar. Can you confirm on your local? – yakya Jun 21 '17 at 14:45
@sotn, yes the `TestFunc2` with `varchar` is deterministic on my test server (Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64) May 27 2016 15:33:17 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) ). Maybe collation of the database plays its role. In my case database collation is `SQL_Latin1_General_CP1_CI_AS`. – Vladimir Baranov Jun 22 '17 at 00:31
I use Microsoft SQL Server 2014 (SP1-GDR) (KB3194720) - 12.0.4232.0 (X64) Sep 23 2016 18:45:14 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) with `Turkish_CI_AS`. I guess it's because of db collation then. Another mysterious behavior of SQL Server's determinism checks... – yakya Jun 22 '17 at 08:07
1

@sotn, try to use `Turkish_BIN2` instead of `Latin1_General_BIN2`. Can you even store characters like `ö` and `ı` in a `varchar` column with Turkish collation? It is quite possible that conversion from Turkish codepage into Latin codepage is ambiguous. In the end, you can always cast your `varchar` column into `varbinary` and replace characters based on their binary ASCII (or whatever is used for Turkish characters) codes. – Vladimir Baranov Jun 22 '17 at 10:11

Deterministic string replace and collate

1 Answers1