Funkce pro odstran∞nφ diakritiky k vyhledßvßnφ v MS SQL
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:
(
@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 %:
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 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:
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φ.
