3

I have the following issue with accent insensitive search in Czech language. My database is set to Czech_CI_AI collation. For some diacritics system works correctly (ie. I), but for some does not (ie. R). In my database there are several 'DVORA*' records:

SELECT contact_name 
FROM   CONTCTSM1 
WHERE  CONTACT_NAME LIKE 'DVO%' 

Results:

DVOŘÁČKOVÁ,
IVETA DVOŘÁČKOVÁ,
JIŘINA DVOŘÁK

2

3 Answers 3

6

R and Ř are different letters, as opposed to modified base letters(?) like German umlauts with ö vs o

From Czech language on Wikipedia (my bold)

The acute accent (čárka) letters (Á, É, Í, Ó, Ú, Ý) and the kroužek letter Ů all indicate long vowels. They have the same alphabetical ordering as their non-diacritic counterparts. ...The háček (ˇ) indicates historical palatalization of the base letter. The letters Č, Ř, Š, and Ž currently represent postalveolar consonants and are ordered behind their corresponding base letters; while Ď, Ň, Ť represent palatal consonants and have the same alphabetical ordering as their non-diacritic counterparts.

A test with Ö and O in Swedish (different letters) and German (modified)

SELECT 'Finnish_Swedish_100_CI_AI' 
          WHERE N'Ö' COLLATE Finnish_Swedish_100_CI_AI = N'O'
UNION ALL
SELECT 'Latin1_General_CI_AI' 
          WHERE N'Ö' COLLATE Latin1_General_CI_AI = N'O'
2

Well, that's all nice and dandy, but while R and Ř should be treated differently while sorting, they should be considered the same when comparing "accent insensitively". Dvořák should be sorted after Dvorak and even "dobře" after "dobrý", it makes no sense whatsoever no to find "Dvořák" when searching for "Dvorak" ignoring accents. As it is, the accent insensitive Czech collations are worse than useless.

1

SQL collation seems not very useful accent insensitive search.

The best solution seems to be to convert the string to varchar with collation SQL_Latin1_General_CP1251_CI_AS.

Either as a persistent column holding varchar value with the 1251 collation or cast the value directly in query (if you don't care that it will go through whole table since there won't be an index).

WHERE 
    (cast([name] as varchar(255))) COLLATE SQL_Latin1_General_CP1251_CI_AS 
    like 
    (cast(N'dvoř%' as varchar(255)))  COLLATE SQL_Latin1_General_CP1251_CI_AS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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