Autor, Název akce Databázové systémy a SQL Lekce 8 Daniel Klimeš Autor, Název akce Vyhledávání v textu Daniel Klimeš, Databázové systémy a SQL •Operátor LIKE • zástupné znaky • _ = 1 libovolný znak •% = 0 nebo n libovolných znaků • ESCAPE '\' • • • Příklad: •Pracoviště Ústí •SELECT * FROM sites WHERE site LIKE ‘%Ústí%’ •Text obsahující znak procento • SELECT * FROM eav_string WHERE value LIKE '%\%%' ESCAPE '\'; • Jednoznakové texty • SELECT * FROM eav_string WHERE value LIKE ‘_’; • Text podobný datumu kdekoliv v textu • SELECT * FROM eav_string WHERE value LIKE ‘%__.__.____%’; • Autor, Název akce Regulární výrazy Daniel Klimeš, Databázové systémy a SQL Regulární výraz = šablona/vzor (pattern) Skládá se: • z hledaných znaků, textu • zástupných znaků • kvantifikátorů • modifikátory • operátory Oracle funkce: •WHERE REGEXP_LIKE(sloupec, ‘reg. vyraz’) •WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$') •Pochází z programovacích jazyků pro zpracování textu •Nejen pro databáze Autor, Název akce Zástupné znaky Daniel Klimeš, Databázové systémy a SQL Znak Význam . (tečka) Jakýkoliv znak ^ Začátek řetězce $ Konec řetězce \d Číslice \D Vše kromě číslice \w Písmeno, číslice, podtržítko \W Doplněk k \w \s Bílý znak – mezera, tabulátor \S Doplněk k \s Hledání datumu: SELECT value FROM eav_string WHERE REGEXP_LIKE(value, '\d\d\.\d\d\.\d\d\d\d') Autor, Název akce Kvantifikátory, modifikátory Daniel Klimeš, Databázové systémy a SQL Znak Význam * 0 – n opakování + 1 – n opakování ? 0 nebo 1 opakování {m} Přesně m opakování {m,} m nebo více opakování {m,n} Minimálně m, maximálně n opakování Znak Význam i Case insensitive hledání c Case sensitive hledání select value from eav_string WHERE REGEXP_LIKE(value, '\d{1,2}\.\d{1,2}\.\d{2,4}') Autor, Název akce Operátory Daniel Klimeš, Databázové systémy a SQL Znak Význam [abc] Jeden z uvedených znaků (a nebo b nebo c) [^abc] Libovolný znak kromě uvedených (vše kromě a b c) (abc) Uzavření skupiny znaků-blok | nebo \1 Odkaz na první blok \ Ruší speciální význam znaku např.: „\.“ = tečka Dvě stejné číslice za sebou SELECT value FROM eav_string WHERE REGEXP_LIKE(value, '(\d)\1') SELECT value FROM eav_string WHERE REGEXP_LIKE(value, '[0123]?\d\.[01]?\d\.\d{2,4}') Autor, Název akce Extrakce řetězce Daniel Klimeš, Databázové systémy a SQL Extrakce subřetězce: REGEXP_SUBSTR(sloupec, pattern, hledat_od, vyskyt, modifikator) Extrakce pozice subřetězce: REGEXP_INSTR(sloupec, pattern, hledat_od, vyskyt, navratova_hodnota, modifikator) Hledat_od – pořadí znaku, od kterého hledat, 1 = od začátku (default) Vyskyt – kolikátý výskyt vrátit, 1 = první (default) Modifikátor – c = case sensitive, i= case insensitive Návratová_hodnota – 0 = vrátí pořadí prvního znaku nalezeného vzoru, 1 = vrátí pořadí prvního znaku za nalezeným vzorem Autor, Název akce Extrakce a konverze Daniel Klimeš, Databázové systémy a SQL SELECT REGEXP_SUBSTR(value, '[0123]?\d\.[01]?\d\.\d{2,4}') FROM eav_string WHERE REGEXP_LIKE(value, '[0123]?\d\.[01]?\d\.\d{2,4}') SELECT TO_DATE(datum, 'dd.mm.yyyy') FROM ( SELECT REGEXP_SUBSTR(value, '[0123]?\d\.[01]?\d\.\d{2,4}') datum FROM eav_string WHERE REGEXP_LIKE(value, '[0123]?\d\.[01]?\d\.\d{2,4}')) Pokus o konverzi může selhat, pokud nejde o platné datum Konverze na datum: ORACLE nemá funkci, která by testovala, zda lze text konvertovat na datum, ale… Autor, Název akce Uživatelská funkce Daniel Klimeš, Databázové systémy a SQL CREATE OR REPLACE FUNCTION STUDENT.jetodatum (p_str IN VARCHAR2 ,format_datumu IN VARCHAR2) RETURN DATE IS BEGIN RETURN TO_DATE(p_str, format_datumu); EXCEPTION WHEN OTHERS THEN RETURN NULL; END; / • •PLSQL procedura/funkce může obsahovat blok výjimek (exception) , který odchytává chyby při běhu programu Autor, Název akce Použití uživatelské funkce Daniel Klimeš, Databázové systémy a SQL SELECT TO_DATE(datum, 'dd.mm.yy'), value FROM ( SELECT REGEXP_SUBSTR(value, '[0123]?\d\.[01]?\d\.\d{2,4}') datum, value FROM eav_string WHERE REGEXP_LIKE(value, '[0123]?\d\.[01]?\d\.\d{2,4}')) WHERE jetodatum(datum, 'dd.mm.yyyy') IS NOT NULL •Lépe zpracovat zvlášť dvojciferné a 4-ciferné roky SELECT REGEXP_SUBSTR(REGEXP_SUBSTR(value, '[0123]?\d\.[01]?\d\.\d{2}(\D|$)'),'[0123]?\d\.[01]?\d\.\d{2}'), value FROM eav_string WHERE REGEXP_LIKE(value, '[0123]?\d\.[01]?\d\.\d{2}(\D|$)'); SELECT REGEXP_SUBSTR(value, '[0123]?\d\.[01]?\d\.\d{4}'), value FROM eav_string WHERE REGEXP_LIKE(value, '[0123]?\d\.[01]?\d\.\d{4}'); Autor, Název akce REGEXP_COUNT, REGEXP_REPLACE Daniel Klimeš, Databázové systémy a SQL REGEXP_COUNT(sloupec, pattern, hledat_od, modifikator) Vrací počet výskytů vzoru: Nahrazení nalezeného vzoru za jiný text: REGEXP_REPLACE(sloupec, pattern,novy_text, hledat_od, vyskyt, modifikator) vyskyt – kolikátý výskyt nahradit, 0 = všechny SELECT REGEXP_REPLACE(value, '([0123]?\d)\.([01]?\d)\.(\d{4})', '\3-\2-\1') datum, value FROM eav_string WHERE REGEXP_LIKE(value, '[0123]?\d\.[01]?\d\.\d{4}') SELECT REGEXP_COUNT(value, '[0123]?\d\.[01]?\d\.\d{4}') datum, value FROM eav_string WHERE REGEXP_LIKE(value, '[0123]?\d\.[01]?\d\.\d{4}') Autor, Název akce FIREBIRD – testovací databáze Tabulka Pacienti •ID •Jmeno •Datum_narozeni •Pohlavi Tabulka Vysetreni •ID_vysetreni •ID •Datum_vysetreni •Typ_vysetreni • Vysledek Minulé cvičení: Vytvořte skript, který 1.Vytvoří tabulku „prvni_vysetreni“ – sloupce ID, Jmeno, Datum_narozeni, Pohlavi, Datum_vysetreni, typ vysetreni, vysledek 2.Přenese první vyšetření každého pacienta (podle datumu vyšetření) ze spojených tabulek Pacienti, Vysetreni do této nové tabulky 3.Smaže všechny muže 4.Změní výsledek 0 u všech záznamů na 1 Daniel Klimeš, Databázové systémy a SQL Autor, Název akce FIREBIRD – testovací databáze Daniel Klimeš, Databázové systémy a SQL CREATE TABLE prvni_vysetreni ( ID Integer, Jmeno varchar(30), Datum_narozeni timestamp, Pohlavi char(1) , Datum_vysetreni timestamp, Typ_vysetreni smallint, Vysledek smallint ); INSERT INTO prvni_vysetreni (ID, Jmeno, Datum_narozeni, Pohlavi, Datum_vysetreni, typ_vysetreni, vysledek) SELECT p.ID, p.Jmeno, p.Datum_narozeni, p.Pohlavi, v.Datum_vysetreni, v.typ_vysetreni, v.vysledek FROM pacienti p, vysetreni v WHERE p.id = v.id AND NOT EXISTS ( SELECT * FROM vysetreni v2 WHERE v2.id = v.id AND v2.datum_vysetreni < v.datum_vysetreni); DELETE FROM prvni_vysetreni where pohlavi = 'M'; UPDATE prvni_vysetreni SET vysledek = 1 WHERE vysledek = 0;