Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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; for example:

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 persisted value in a column.

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

Q2. (Bonus Question) The reason I do this computed persisted column is 1)to search 2)to detect duplicates. For example the user may enter the customer's last name as either 'Gagne' or 'Gagné' or 'GAGNE' or 'GAGNÉ' and the app will find it using the persisted computed column (and the update/insert will balk at the duplicate). Is there a better way to do this?

EDIT: Using SQL Server 2012 and SQL-Azure.

share|improve this question
2  
What are your plans if you'd get another a customer named Gagné? –  vonPryz Oct 3 '13 at 14:09
    
Well, you could create an INSERT and an UPDATE trigger on that table to just simply populate a "normal" column that contains no special characters ... –  marc_s Oct 3 '13 at 14:17
add comment

1 Answer

Why not just create the table column with a case-insensitive, accent-insensitive collation? This prevents duplicates according to the collation rules, and allows the sort of searches you seem to require:

CREATE TABLE Test
(
    col1 varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI PRIMARY KEY
);

-- Success
INSERT dbo.Test VALUES ('Gagné');

-- Failed, duplicate key
INSERT dbo.Test VALUES ('Gagne');

-- Success
SELECT * 
FROM dbo.Test 
WHERE col1 = 'gaGNE';

As an important side note, I should mention that T-SQL scalar and multi-statement functions have very poor performance characteristics. Using them in a computed column definition is even worse. Avoid.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.