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 values LIKE '%\%%' ESCAPE '\'; • Jednoznakové texty • SELECT * FROM eav_string WHERE values LIKE ‘_’; • Text podobný datumu kdekoliv v textu • SELECT * FROM eav_string WHERE values 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 • Pochází z programovacích jazyků pro zpracování textu • Nejen pro databáze Autor, Název akce PostgreSQL Daniel Klimeš, Databázové systémy a SQL https://www.postgresql.org/docs/current/static/functions-matching.html Operator Description Example ~ Matches regular expression, case sensitive 'thomas' ~ '.*thomas.*' ~* Matches regular expression, case insensitive 'thomas' ~* '.*Thomas.*' !~ Does not match regular expression, case sensitive 'thomas' !~ '.*Thomas.*' !~* Does not match regular expression, case insensitive 'thomas' !~* '.*vadim.*' • WHERE sloupec ~ ‘vyraz’ • SUBSTRING (string FROM pattern) • REGEXP_REPLACE(string text, pattern text, replacementtext [, flags text]) 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 values FROM eav_string WHERE values ~ '\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í SELECT values FROM eav_string WHERE values ~ '\d{1,2}\.\d{1,2}\.\d{4}' Autor, Název akce Greedy x non-greedy Daniel Klimeš, Databázové systémy a SQL SELECT values, SUBSTRING(values from '\d.*\d') greedy, SUBSTRING(values, '\d.*?\d') non_greedy FROM eav_string WHERE values ~ '\d.*\d' Znak Význam * 0 – n opakování + 1 – n opakování ? 0 nebo 1 opakování {m,} m nebo více opakování {m,n} Minimálně m, maximálně n opakování 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 (11, 22, 33,…) SELECT values FROM eav_string WHERE values ~ '(\d)\1' SELECT values FROM eav_string WHERE values ~ '[0123]?\d\.[01]?\d\.\d{4}' Autor, Název akce Extrakce řetězce Daniel Klimeš, Databázové systémy a SQL Extrakce subřetězce: SUBSTRING (string FROM pattern) SELECT SUBSTRING (values from '[0123]?\d\.[01]?\d\.\d{4}'), values FROM eav_string WHERE values ~ '[0123]?\d\.[01]?\d\.\d{4}‘ --pouze první výskyt SELECT REGEXP_MATCHES (values, '[0123]?\d\.[01]?\d\.\d{4}', 'g'), values FROM eav_string WHERE values ~ '[0123]?\d\.[01]?\d\.\d{4}‘ -- pro každý výskyt nový řádek SELECT REGEXP_MATCHES (values, '([0123]?\d\.[01]?\d\.\d{4}).*?([0123]?\d\.[01]?\d\.\d{4})'), values FROM eav_string --WHERE values ~ '[0123]?\d\.[01]?\d\.\d{4}’ --dva výskyty => pole (array) Autor, Název akce Pole (array) Daniel Klimeš, Databázové systémy a SQL • Na položky se odkazujeme indexem v hranatých závorkách • Index od 1 SELECT datumy, datumy[1] prvni_datum, datumy[2] druhe_datum FROM ( SELECT REGEXP_MATCHES (values, '([0123]?\d\.[01]?\d\.\d{4}).*?([0123]?\d\.[01]?\d\.\d{4})') datumy, values FROM eav_string ) a Autor, Název akce Extrakce a konverze Daniel Klimeš, Databázové systémy a SQL Pokus o konverzi může selhat, pokud nejde o platné datum nebo… Konverze na datum: SELECT TO_DATE(SUBSTRING (values from '[0123]?\d\.[01]?\d\.\d{4}'), 'dd.mm.yyyy'), values FROM eav_string WHERE values ~ '[0123]?\d\.[01]?\d\.\d{4}' INSERT INTO eav_string (values) VALUES ('30.02.2016') SELECT TO_DATE (SUBSTRING (values from '[0123]?\d\.[01]?\d\.\d{4}'), 'dd.mm.yyyy'), values FROM eav_string WHERE values = '30.02.2016' Autor, Název akce Možné odhalení nežádoucích konverzí Daniel Klimeš, Databázové systémy a SQL SELECT datum, s, TO_CHAR(datum, 'FMdd.FMmm.yyyy') bez_nul, TO_CHAR(datum, 'dd.mm.yyyy') plne, values FROM ( SELECT TO_DATE(SUBSTRING (values from '[0123]?\d\.[01]?\d\.\d{4}'), 'dd.mm.yyyy') datum, SUBSTRING (values from '[0123]?\d\.[01]?\d\.\d{4}') s, values FROM eav_string WHERE values ~ '[0123]?\d\.[01]?\d\.\d{4}' ) a WHERE s <> TO_CHAR(datum, 'FMdd.FMmm.yyyy') AND s <> TO_CHAR(datum, 'dd.mm.yyyy') Autor, Název akce REGEXP_REPLACE Daniel Klimeš, Databázové systémy a SQL Nahrazení nalezeného vzoru za jiný text: REGEXP_REPLACE(sloupec, pattern, novy_text, modifikator) modifikator– ‘g’ = všechny výskyty SELECT REGEXP_REPLACE(values, '([0123]?\d)\.([01]?\d)\.(\d{4})', '\3-\2-\1') datum, values FROM eav_string WHERE values ~ '[0123]?\d\.[01]?\d\.\d{4}' Autor, Název akce Rozklad pomocí reg. výrazu Daniel Klimeš, Databázové systémy a SQL SELECT foo FROM REGEXP_SPLIT_TO_TABLE('the quick brown fox jumps over the lazy dog', '\s+') AS foo; Autor, Název akce Odkazy Daniel Klimeš, Databázové systémy a SQL •http://www.regularnivyrazy.info/ •http://www.regexlib.com •Jan Goyvaerts: Regulární výrazy Autor, Název akce Cvičení Daniel Klimeš, Databázové systémy a SQL • Obsahuje tabulka PSČ? • Obsahuje tabulka Rodná čísla? Autor, Název akce Daniel Klimeš, Databázové systémy a SQL SELECT values FROM eav_string WHERE values ~ '^\d{6}/\d{4}' SELECT values FROM eav_string WHERE values ~ '^[1-7]\d{2}\s?\d{2}\s*$'