SELECT záznamu z tabulky s diaktrikou i bez

SELECT záznamu z tabulky s diaktrikou i bez
« kdy: 28. 01. 2025, 21:03:22 »
Zdravím.
Potřeboval bych poradit.
Existuje možnost udělat SELECT záznamů z tabulky s diakritikou i bez?
Potřeboval bych aby SQL dotazy

Kód: [Vybrat]
SELECT * FROM profile WHERE last_name LIKE 'jašková';
a
Kód: [Vybrat]
SELECT * FROM profile WHERE last_name LIKE 'jaskova';

vraceli stejný výsledek.

DB: Sqlite3


hknmtt

  • ***
  • 212
    • Zobrazit profil
    • E-mail
Re:SELECT zaznamu z tabulky s diaktrikou i bez
« Odpověď #1 kdy: 28. 01. 2025, 21:16:05 »
Urcite nie. "a" a "á" su naprosto odlisne znaky. Musis to transliterovat pred tym, nez to ulozis do db.

Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #2 kdy: 28. 01. 2025, 22:29:54 »
a pochopitelně mít uložené oba sloupce.

robac

  • ***
  • 206
    • Zobrazit profil
    • E-mail

hknmtt

  • ***
  • 212
    • Zobrazit profil
    • E-mail
Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #4 kdy: 29. 01. 2025, 01:00:16 »


Re:SELECT zaznamu z tabulky s diaktrikou i bez
« Odpověď #5 kdy: 29. 01. 2025, 06:53:40 »
Urcite nie. "a" a "á" su naprosto odlisne znaky. Musis to transliterovat pred tym, nez to ulozis do db.
To chápu. Myslel jsem že existuje nějaký extension pro sqlite který udělá transliterace "on the fly".

Každopádně, děkuji za odpovědi.

jjrsk

  • *****
  • 654
    • Zobrazit profil
Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #6 kdy: 29. 01. 2025, 08:34:44 »
Collation pomoze len pri "a" vs "A" ale nie "a" vs "á".

Nikolivek, collation to resi.
https://dba.stackexchange.com/questions/190969/ignore-accents-in-where-clause

Samozrejme za predpokladu ze to prislusna databaze umi.

Da se to pochopitelne obejit, jen pak zalezi na tom jak moc je tam zaznamu, a jak moc jde o vykon toho obchazeni. Pro cestinu je to celkem jednoduchy, ale obchazet to obecne by bylo fakt drahy.
« Poslední změna: 29. 01. 2025, 08:36:21 od jjrsk »

robac

  • ***
  • 206
    • Zobrazit profil
    • E-mail
Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #7 kdy: 29. 01. 2025, 09:22:15 »
https://www.sqlite.org/datatype3.html#collation
Collation pomoze len pri "a" vs "A" ale nie "a" vs "á".
Staci si to precist a overite si, ze nemate pravdu.

hknmtt

  • ***
  • 212
    • Zobrazit profil
    • E-mail
Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #8 kdy: 29. 01. 2025, 10:30:39 »
https://www.sqlite.org/datatype3.html#collation
Collation pomoze len pri "a" vs "A" ale nie "a" vs "á".
Staci si to precist a overite si, ze nemate pravdu.

Napriklad "á" nie je sucastou ascii, ktore sa prave spomina v uvedenom odkaze, ale az extended ascii https://www.ascii-code.com/CP1252/225 Navyse sa v uvedenom odkaze vyslovene uvadza "Hence the 26 upper case characters of ASCII are folded to their lower case equivalents", cize jasne sa hovori o normalnom ascii, ktore "á" nema. V povodnej otazke je napriklad "š" ktore tiez v ascii nie je.

Ako bolo uvedene vyssie, collation ma aj akcenty, ale kedy "a", "á" a "A" mozu byt definovane ako ekvivalenty. Co je pri radeni vysledkov uzitocne, ale nemyslim(nehovorim ze viem, lebo som to netestoval), ze tu je to aplikovatelne na index pre select query kedy sa hlada ekvivalent. Mozno ak sa spravi select s TRANSLATE() alebo nejakym inym modifikatorom, tak by to islo vyriesit ale to sme potom znovu pri tej transliteracii, ktoru je lepsie riesit priamo na urovni dat uchovanych v db prave na ucely indexovania.

Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #9 kdy: 29. 01. 2025, 19:06:31 »
Spránná odpověď pro SQLite je, že to nativně na rozdíl od větších databází neumí.
SQLite zná jen 3 varianty collation (BINARY, NOCASE, RTRIM) a ani jedna z nich neignoruje diakritiku.
Možná řešení:
- Použití vlastní collation – můžeš vytvořit vlastní porovnávací funkci v C pomocí sqlite3_create_collation, která normalizuje text (např. odstraní diakritiku).
- Normalizace textu v aplikaci – před uložením do databáze (např. převod na ASCII v C#, Pythonu, PHP…).
- Použití externí knihovny – některé SQLite rozšíření (např. ICU) podporují pokročilejší collation.
- Přejít na jinou databázi (PostgreSQL, MySql, MariaDb, ...), která to umí

Kdysi jsem taky trochu víc řešil výběr správné collation v MariaDB, když jsem požadoval shodu např i pro O = Ø, ae = æ.  A našel :)

mikrom

  • ****
  • 374
    • Zobrazit profil
    • E-mail
Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #10 kdy: 29. 01. 2025, 21:10:02 »
Zdravím.
Potřeboval bych poradit.
Existuje možnost udělat SELECT záznamů z tabulky s diakritikou i bez?
Potřeboval bych aby SQL dotazy

Kód: [Vybrat]
SELECT * FROM profile WHERE last_name LIKE 'jašková';
a
Kód: [Vybrat]
SELECT * FROM profile WHERE last_name LIKE 'jaskova';

vraceli stejný výsledek.

DB: Sqlite3

A co naistalovat sqlite3-pcre a pouzit REGEXP ?
Kód: [Vybrat]
sqlite> select * from TMP_TABLE where name REGEXP 'ja(s|š)kov(a|á)';
1|jaskova
2|jašková

jjrsk

  • *****
  • 654
    • Zobrazit profil
Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #11 kdy: Dnes v 12:00:11 »
...
A co naistalovat sqlite3-pcre a pouzit REGEXP ?
To je presne jeden z prikladu tech drahych reseni jak to obejit.

Ostatne sqlite je proto "lite", ze se nepredpoklada, ze od toho bude kdokoli chtit cokoli jinyho nez nejzakladnejsi primitiva. Jinak receno jediny spravny reseni je vymenit tu databazi.

BTW: Chtel bych videt, jak ten vyraz budes konstruovat, protoze to co dostanes bude "ěšžžůéíť" ... takze budes muset mit nekde definovanou jeste taky nejakou prekladovou tabulku.

Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #12 kdy: Dnes v 13:09:31 »
Možná řešení:
- Použití vlastní collation – můžeš vytvořit vlastní porovnávací funkci v C pomocí sqlite3_create_collation, která normalizuje text (např. odstraní diakritiku).
- Normalizace textu v aplikaci – před uložením do databáze (např. převod na ASCII v C#, Pythonu, PHP…).
- Použití externí knihovny – některé SQLite rozšíření (např. ICU) podporují pokročilejší collation.
- Přejít na jinou databázi (PostgreSQL, MySql, MariaDb, ...), která to umí

Možná se pletu, kdyžtak mě opravte, ale collation se nepoužívá na operátory like nebo glob, ne? Jen na rovnost, nerovnost.
Takže by to nefungovalo třeba na "napovídání", kdy tam třeba uživatel může napsat jen část toho řetězce a rovnout to filtruje. Třeba to tazatel nechce, nevím.

Druhá varianta mi přijde nejschůdnější a bude s největší pravděpodobností i nejrychlejší.
Už jsem to někde takhle dělal, dokonce do samostatné tabulky, kde bylo ID a názvy bez diakritiky, co s tou původní pak byla jen provázaná.

Netuším, co tazatel používá za jazyk/prostředí, ale pokud by se chtěl z jakéhokoliv důvodu vyhnout duplikaci dat, tak už mi přijde možná lepší custom skalární funkce, která prostě vstupní řetězec zbaví diakritiky (mě na se tohle osvědčilo přes nějakou unicode knihovnu převést řetězec do NFKD denormalizovaného tvaru, což rozdělí všechny znaky s diakritikou na základní písmeno a akcent, a pak se zbavit všech znaků s kódem >127.)
U custom collation se posílají dvě hodnoty a vrací se výsledek, tady by ten overhead mohl být podobný (i když záleží jestli by to dělal třeba v Pythonu nebo C) a kolik má dat, akorát by to mohlo fungovat vcelku univerzálně s jakýmkoliv operátorem.

...
BTW: Chtel bych videt, jak ten vyraz budes konstruovat, protoze to co dostanes bude "ěšžžůéíť" ... takze budes muset mit nekde definovanou jeste taky nejakou prekladovou tabulku.

Tak taky by se to dal ten regex udělat. Vezme se vstupní řetězec, zbaví se diakritiky (viz to NFKD výše). Pak se po znacích porovná vstupní řetězec s tím bez akcentů a zkonstruuje se případně ten regex výraz.
Jak to bude rychlé/pomalé, je samozřejmě otázka testu. I ty varianty s custom collation nebo skalární funkcí budou mít svůj overhead.

Jak jsem zmiňoval a bylo to i v předchozích postech, já bych se klonil k druhému uložení sloupce bez diakrtitiky, pokud by to mělo být co nejefektivnější.


jjrsk

  • *****
  • 654
    • Zobrazit profil
Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #13 kdy: Dnes v 16:52:06 »
...
Jak jsem zmiňoval a bylo to i v předchozích postech, já bych se klonil k druhému uložení sloupce bez diakrtitiky, pokud by to mělo být co nejefektivnější.
Jenze tohle ti bude fungovat jen a pouze s nekolika malo jazyky.

Re:SELECT záznamu z tabulky s diaktrikou i bez
« Odpověď #14 kdy: Dnes v 17:09:25 »
SQLite nepodporuje unaccent ako PosgreSQL treba si vypomoct funkciou,
alebo mozes pozuit extension ktora tuto funkciu doplna
https://github.com/nalgeon/sqlean