sql-servercodepagesaccent-insensitive

How do I perform an accent insensitive compare in SQL Server for 1250 codepage


There are already sever question and solution on accent insensitive search on stackoverflow, but none of them work for codepage 1250 (Central European and Eastern European languages).

How do I perform an accent insensitive compare (e with è, é, ê and ë) in SQL Server?

LINQ Where Ignore Accentuation and Case

Ignoring accents in SQL Server using LINQ to SQL

Modify search to make it Accent Insensitive in SQL Server

Questions about accent insensitivity in SQL Server (Latin1_General_CI_AS)

The problem is that accent insensitive collation are bidned to some specific codepages and that I am missing accent insensitive collation for 1250 codepage in MSDN documentation.

I need to modify the collation of the column to make Entity Framework working in accent insensitive way.

For example if I change a collation to SQL_LATIN1_GENERAL_CP1_CI_AI, c with accute is select as c without accute (U+0107) because wrong codepage.

How to solve this?


Solution

  • SELECT *
    FROM   sys.fn_helpcollations()
    WHERE  COLLATIONPROPERTY(name, 'CodePage') = 1250
           AND description LIKE '%accent-insensitive%';
    

    Returns 264 results to choose from.

    Picking the first one

    SELECT N'è' COLLATE Albanian_CI_AI
    UNION
    SELECT N'é'
    UNION
    SELECT N'ê'
    UNION
    SELECT N'ë' 
    

    returns a single row as desired (showing all compared equal)