Regex (MySQL)

Uit De Vliegende Brigade
Ga naar: navigatie, zoeken

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
-- ===========================
--
"\\(.+\\)"

Zie ook

Bronnen