Regex (MySQL)
MySQL kent reguliere expressies. Deze kun je gebruiken als selectiecriteria. Je kunt ze helaas niet gebruiken voor extractie van tekenreeksen, bv. substrings.
Algemene formulering bij gebruik met select-statements:
select * from tbl where fld regexp 'expressie';
Match substring
select vld_a from tbl_t where vld_a regexp '999'; -- Alle strings waar '999' in voorkomt
Match beginning of string ^
select vld_a from tbl_t where vld_a regexp '^0'; -- Alle string die beginnen met 0
Match ending of string $
select vld_a from tbl_t where vld_a regexp '5$'; -- Alle string die eindigen op 5
Match begin & eind
-- ^0 - Beginnend met een 0 -- .* - Daarna een willekeurig aantal (*) willekeurige tekens (.) -- 3$ - Eindigend met een 3 -- ============================================================== -- select vld_a from tbl_t where vld_a regexp '^0.*3$';
-- Alle records met maar 1 cijfer: -- =============================== -- select * from tbl_i where vld_a regexp "^[0-9]$";
Match OR |
-- Alle string met '000' of '999' -- ============================== -- '000|999'
-- Selecteer tekenreeksen die:
--
-- * Beginnen met het woord ''Koolborstelset''
-- * Gevolgd door een nummer van vier cijfers dat begint met een 3, 4, 5 of 6
-- * Daarna niets
-- ==========================================================================
--
'^Koolborstelset (3|4|5|6)[0-9]{3}$'
Match AND
Helaas: Er bestaat geen AND-functie [1].
Precedence ()
select vld_a from tbl_t where vld_a regexp '^0|9'; -- Alle string die beginnen met 0, of waarin '9' voorkomt select vld_a from tbl_t where vld_a regexp '^1|^9'; -- Alle string die beginnen met 1, of beginnen met 9 select vld_a from tbl_t where vld_a regexp '^(1|9)'; -- Alle string die beginnen met 1, of beginnen met 9 select vld_a from tbl_t where vld_a regexp '^(1|9)3'; -- Alle string die beginnen met 13 of 19
Wildcard .
De wildcard "." matched met elk teken, maar niet met NULL. Het geldt voor maar één positie:
select vld_a from tbl_t where vld_a regexp '.' -- Alle niet-lege strings van willekeurige lengte>0 select vld_a from tbl_t where vld_a regexp '.*' -- Alle lege en niet-lege strings, want * betekent dat 0 keer matchen ook ok is
Voorbeelden:
select * from root where sku regexp "." -- Alle records, behalve als sku-veld leeg is select * from root where sku regexp ".*" -- Alle records select * from root where sku regexp "_._" -- Alle records met twee underscores met daartussen één teken! select * from root where sku regexp "_.*_" -- Alle records met twee underscores
Casus mei 2017:
-- Alle records -- ========================================= -- select * from import_tmp -- 35.115 records -- Alle records met een "_" in de sku -- Deze uitdrukkingen zijn identiek: -- ========================================= -- select * from import_tmp where sku regexp "_"; -- 34.484 records select * from import_tmp where sku regexp ".*_.*"; -- 34.484 records -- Alle records met twee underscores -- ========================================= -- select * from import_tmp where sku regexp "_.*_"; -- 34.484 records -- Alle records met drie undercores -- ========================================= -- select * from import_tmp where sku regexp "_.*_.*_"; -- 19.870 records -- Alle records met drie underscores en -- minimaal 1 teken na de laatste underscore -- ========================================= -- select * from import_tmp where sku regexp "_.*_.*_."; -- 16.862 records
Soorten tekens
[0-9] -- Cijfers 0-9 [a-zA-Z] -- Alle letters, zoals klein als groot [a-zA-Z0-9] -- Alle letters & cijfers [a-zA-Z0-9 ] -- Alle lettters, cijfers en spaties
Voorbeeld:
-- Begin met een code van minimaal 6 cijfers/letters/spaties, daarna ", " en een code van drie tekens
-- Voorbeelden van matches:
--
-- 606209000, 001, 190, 250, 420
-- 9 617 081 541, 542
--
^[a-zA-Z0-9 ]{6,}, [a-zA-Z0-9]{3}
Voorbeeld:
-- Alle sku's met twee underscores en een code zoals 0 123 456 789
--
select * from import_tmp where sku regexp "^.*_.*_[0-9] [0-9]{3} [0-9]{3} [0-9]{3}.*"
Aantallen * + ?
? - 0 of 1 keer * - 0 of meer + - 1 of meer
Herhalingen {}
-- Alle strings met 6 cijfers op rij
-- =================================
--
select vld_a from tbl_t where vld_a regexp '[0-9]{6}';
-- Alle string met 12 cijfers op rij
-- Die beginnen met een 3, gevolgd door 5 cijfers
-- Daarna weer een 3, gevolgd door 5 cijfers
-- ==============================================
--
select vld_a from tbl_t where vld_a regexp '3[0-9]{5}3[0-9]{5}';
-- Selecteer strings met drie of meer spatie's.
-- Gevolgd door eventueel een komma, eventueel een punt,
-- eventueel een spatie, gevolgd door een nul
-- ==========================================================
--
select vld_a from tbl_t where vld_a regexp '[ ]{3,},?.?[ ]?0'
-- Minimaal 3 spaties
-- Eventueel een komma
-- Eventueel een punt
-- Eventueel een spatie
-- Gevolgd door een 0 of een 3
-- ============================
--
regexp "[ ]{3,},?.?[ ]?(0|3)"
Escaping
Escapen
( )
Niet escapen
_
Ja maar hoe dan?
Met een dubbele tegenschrap, en soms zelfs met drie tegenschraps, geloof ik:
-- Alles met drie puntjes, dus ... -- =============================== -- "\\.\\.\\."
-- String met: -- -- * Haakje openen -- * Daarna minimaal één teken -- * Haakje sluiten -- =========================== -- "\\(.+\\)"