Funkce pro odstranění diakritiky k vyhledávání v MS SQL

16. února 2005

•

Nejenže se dodnes řada uživatelů počítače nenaučila psát řádně diakritiku, ale občas ani nemají možnost ji psát (používají nevhodně nastavený počítač, počítač v zahraničí bez českého národního prostředí nebo třeba mobilní telefon). Proto při jednoduchém vyhledávání potřebujeme, aby uživatel dostal řádný výsledek nezávisle na tom, zda zadal výraz s diakritikou či bez ní. Zde si ukážeme funkci, pomocí které diakritiku „zahodíme“ přímo v SQL dotazu a vyhledávání tak bude na diakritice nezávislé.

Předem je potřeba si říct, že dále popsaná uživatelská funkce opravdu pouze odstraní diakritiku z řetězce – nehodí se proto pro obsáhlé texty. Velmi dobře ji však upotřebíme u kratších textů ve sloupcích typu CHAR/VARCHAR, případně NCHAR/NVARCHAR – nemusíme se zde uchylovat k trikům jako je duplicitní sloupec obsahující tentýž text bez diakritiky.

K typickým adeptům pro použití naší funkce může patřit třeba tabulka obsahující seznam měst a obcí. Díky naší funkci bude stejný výsledek při zadání „Kardasova Recice“ i „Kardašova Řečice“. Funkci můžeme použít i obráceně – myslím tím případ, kdy uživatel aplikace sice zadává názvy řádně s diakritikou, ovšem my máme ve zdroji dat mírně řečeno nesoulad a texty jsou různě s diakritikou i bez diakritiky, pro zadání „Kardašova Řečice“ se pak korektně vyhledá i záznam obsahující text „Kardasova Recice“.

Jak záhy zjistíme, kód konverzní funkce je prakticky na jednom řádku, pokud si odmyslíme zbývající nutné náležitosti uživatelské funkce:

CREATE FUNCTION [dbo].[RemoveDiacritics]
(
@input VARCHAR(1024)
)
RETURNS VARCHAR(1024)
AS
    BEGIN
            RETURN (SELECT @input COLLATE SQL_Latin1_General_CP1251_CI_AS)
        END

Vtip spočívá v použití definice COLLATE, tedy znakové sady, která neobsahuje diakritické znaky. Microsoft SQL Server na takovéto použití zareaguje po svém – diakritické znaky sám nahradí znaky bez diakritiky, a to právě potřebujeme.

Ukažme si, jakým způsobem použít funkci při vyhledávání. Máme zde příklad, ve kterém chceme získat záznamy, kde část textu zadaného uživatelem odpovídá názvu firmy nebo městu či adrese. Použijeme operátor LIKE s využitím zástupného symbolu %:

SELECT [Company],[City],[Address],[Zip]
FROM [dbo].[Contacts]
WHERE
dbo.RemoveDiacritics([Company]) LIKE ‚%Kardasova Recice%‘
OR
dbo.RemoveDiacritics([City]) LIKE ‚%Kardasova Recice%‘
OR
dbo.RemoveDiacritics([Address]) LIKE ‚%Kardasova Recice%‘

Všimněte si, že název obce je zadán bez diakritiky – je to logické, neboť porovnáváme záznam z tabulky databáze ořezaný o diakritiku se zadaným textem a ten tedy musí od uživatele projít nějakou úpravou, aby byl také bez diakritiky. Hledaný text proto musíme do dotazu zadávat vždy bez diakritiky – aplikaci je potřeba doplnit o vhodnou konverzní funkci. V krajním případě by v aplikacích pro intranet bylo možné použít odstranění diakritiky už přímo na klientské straně, obdobně jako při práci s textem pro SMS bránu. Dobře použitelnou funkci pro aplikace v PHP najdete v užitečných konverzní funkcích v příkladech. Jen pro doplnění přidávám vlastní funkci v C#, kterou používám v ASP.NET aplikacích:

String RemoveDiacritics(string myText)
{
String diacritic = „áäčďéěíĺľňóôőöŕšťúůűüýřžÁÄČĎÉĚÍĹĽŇÓÔŐÖŔŠŤÚŮŰÜÝŘŽ“;
String diacRemoved = „aacdeeillnoooorstuuuuyrzAACDEEILLNOOOORSTUUUUYRZ“;
StringBuilder finalText = new StringBuilder(String.Empty);
for(Int16 i=0;i<myText.Length; i++)
{
    Int32 myPosition = diacritic.IndexOf(myText[i]);
    if (myPosition != -1)
      finalText.Append(diacRemoved[diacritic.IndexOf(diacritic[myPosition])]);
    else
      finalText.Append(myText[i]);
}
return finalText.ToString();
}

Jak jsem už psal, funkce se může hodit i pro „zamaskování nepořádku“ v našem zdroji dat – pokud z nějakého důvodu tabulky nejsou řádně normalizované, skutečně se může vyskytnout stav, kdy se v tabulce nachází město v různých exemplářích s různě zadanou diakritikou (například Děčín i DECIN). Následující dotaz ukazuje, jak se můžeme při výpisu všech měst zbavit těch „nediakritických“, pokud takové město existuje i s diakritikou:

SELECT [City]
FROM [dbo].[Contacts]
WHERE [City] NOT IN
(
    SELECT dbo.RemoveDiacritics([City])
    FROM [dbo].[Contacts]
    WHERE dbo.RemoveDiacritics([City]) <> [City]
)
GROUP BY [City]
ORDER BY 1

Daný dotaz vytáhne všechna unikátní města (agregujeme pomocí GROUP BY, výsledek pomocí ORDER BY podle prvního sloupce setřídíme) s tím, že všechny záznamy, které mají nějaký svůj diakritický protějšek (po ořezání diakritiky je text rozdílný oproti původnímu textu) vybrány nejsou.

Funkce funguje dobře, pokud máme v tabulce záznamy řádně s diakritikou a úplně bez diakritiky – pokud se nám daný text vyskytuje ve více variantách zápisu diakritiky, pak se nám bohužel ve výstupu tyto různorodé varianty objeví, potlačeny budou pouze ty úplně nediakritické. Například z tabulky obsahující záznamy v podobě „Jičín“, „Jicin“ a „Jicín“ dostaneme jako výsledek záznamy dva, tedy „Jičín“ a „Jicín“. I tak ale myslím, že se hodí funkci použít alespoň do doby, než se nám podaří upravit datový model aplikace a data dostat do korektní podoby bez duplicit.

K dispozici je vám samozřejmě zdrojový kód použitých funkcí.