Using TSQL, CAST () with COLLATE is not deterministic. How to make it deterministic? What job?

Question

I have a function that includes:

SELECT @pString = CAST(@pString AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS 

This is useful, for example, to remove accents in French; eg:

 UPPER(CAST('Éléctricité' AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS) 

gives ELECTRICITE .

But using COLLATE makes the function non-deterministic and therefore I cannot use it as a computed persistent value in a column.

Q1. Is there another (quick and easy) way to remove such accents with a deterministic function?

Q2. (Bonus question) The reason I am doing this calculated thrust column is the search. For example, a user can enter the customer’s name as “Gagne” or “Gagné” or “GAGNE” or “GAGNÉ” and the application will find it using a constant computed column. Is there a better way to do this?

EDIT: Using SQL Server 2012 and SQL-Azure.

Answer 1

You will find that it is actually determinate, it just has a different behavior depending on the character you are trying to match.

Check the Windows 1251 encoding page for the behavior of accepted characters and unacceptable characters.

Here is a sorting chart for Cyrillic_General_CI_AI . This code is 1251 case insensitive and accent. This will show you the mappings for all valid characters in this sort.

Regarding the search issue, as Kate said, I would investigate the possibility of placing the full text index in the column that you are looking for.

Answer 2

The best answer I received is Sebastian Sazharov . I used his example to fix the problem. He suggested VIEW with a UNIQUE INDEX. This gives a good idea of ​​the solution:

 create table Test(Id int primary key, Name varchar(20)) create view TestCIAI with schemabinding as select ID, Name collate SQL_Latin1_General_CP1_CI_AI as NameCIAI from Test create unique clustered index ix_Unique on TestCIAI (Id) create unique nonclustered index ix_DistinctNames on TestCIAI (NameCIAI) insert into Test values (1, 'Sébastien') --Insertion 2 will fail because of the unique nonclustered indexed on the view --(which is case-insensitive, accent-insensitive) insert into Test values (2, 'Sebastien')