Regex (MySQL): verschil tussen versies
(geen verschil)
|
Huidige versie van 24 jan 2021 om 18:40
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';
Niet voor extractie
Je kunt regex niet gebruiken in MySQL versies < 8 voor extractie of find-&-replace [1][2]. Oplossingen:
- Overstappen op MySQL 8.x - Momenteel in ontwikkeling
- UDF - User Defined Functions
- Gebruik een oplossing buiten MySQL, bv. een parser in Python of sed. Zie [3] voor een voorbeeld met mysqldump » sed » mysql-import.
Match substring
select * from tmp where device_id regexp "A"; # Alle strings waar een 'A' in voorkomt select * from tmp where device_id regexp '999'; # Alle strings waar '999' in voorkomt
Match beginning of string ^
select * from tmp where device_id regexp '^0'; # Alle string die beginnen met 0
Match ending of string $
select * from tmp where device_id 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 * from tmp where device_id regexp '^0.*3$';
# Alle records met maar 1 cijfer: # =============================== # select * from tbl_i where device_id 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 [4].
Precedence ()
select * from tmp where device_id regexp '^0|9'; # Alle string die beginnen met 0, of waarin '9' voorkomt select * from tmp where device_id regexp '^1|^9'; # Alle string die beginnen met 1, of beginnen met 9 select * from tmp where device_id regexp '^(1|9)'; # Alle string die beginnen met 1, of beginnen met 9 select * from tmp where device_id 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 * from tmp where device_id regexp '.' # Alle niet-lege strings van willekeurige lengte>0 select * from tmp where device_id regexp '.*' # Alle lege en niet-lege strings, want * betekent dat 0 keer matchen ook ok is select * from tmp where device_id regexp "..."; # Strings die uit minimaal 3 tekens bestaan
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
. # Elk teken (maar 1 positie). Geen NULL [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 [ a-zA-Z0-9] # Idem - De positie van de spatie binnen de string maakt niet uit
Filter numerieke codes
Voorbeeld: Selecteer alle rijen waar device_type uitsluitend uit cijfers bestaat.
Dit geeft niet het gewenste resultaat, want dit statement selecteert alle rijen waar minimaal één cijfer in het veld device_type voorkomt:
select * from sales where device_type regexp "[0-9]+"
Dit is beter: Door ^ (begin) en $ (eind) toe te voegen, geef je aan, dat dit voor de hele string geldt:
select * from sales where device_type regexp "^[0-9]+$"
Meer voorbeelden
# Alle sku's die beginnen met "ca-" en twee cijfers (bv. "ca-00", "ca-78", etc.) ################################################################################ # # Twee varianten met dezelfde uitkomst: # select * from tmp where sku regexp "^ca-[0-9][0-9]" select * from tmp where sku regexp "^ca-[0-9]{2}" # Alle sku's met twee underscores en een code zoals 0 123 456 789 ################################################################################ # # select * from tmp where sku regexp "^.*_.*_[0-9] [0-9]{3} [0-9]{3} [0-9]{3}.*" # Iets meer complex ################################################################################ # # 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}
Aantallen . * + ?
? - 0 of 1 keer * - 0 of meer keer . - 1 keer .. - 2 keer, etc. + - 1 of meer keer , - Of meer bij formuleringen met accolades. Bv. {6,1}
Voorbeelden:
# Alles met 1 teken tussen haakjes #################################### # select * from device_tmp where device_type regexp "\\(.\\)"; # Alles met 5 tekens tussen haakjes ################################### # select * from device_tmp where device_type regexp "\\(.....\\)"; # Alles met 14 tekens tussen haakjes #################################### # select * from device_tmp where device_type regexp "\\(..............\\)"; # Alles met 14 tekens of meer tussen haakjes ############################################ # # * "+" heeft betrekking op het laatste teken ervoor. Hiero dus: # {13 tekens} & {.+} ⇒ Minimaal 14 tekens # * Tweede statement is identiek aan het eerste statement # select brand from device_tmp where brand regexp "\\(..............+\\)"; select brand from device_tmp where brand regexp ".{13}.+";
Herhalingen {}
# Alle strings met 6 cijfers op rij # ================================= # select * from tmp where device_id 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 * from tmp where device_id 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 * from tmp where device_id 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)"
Escapen
Wel
( )
Niet
_
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 # =========================== # "\\(.+\\)"
Voorbeelden
Negen cijfers, gevolgd door komma-gescheiden suffixen
# Criteria: # # * Wel: 123456789, 123 # * Wel: 123456789, 123, 456, 789, 912, 123, 456 # * Niet: 123456789, 12345678907 select device_sku from device_tmp where device_sku regexp "^.{9}, [a-zA-Z0-9]{3}(,|$)"; # Gelukt! select device_sku from device_tmp where device_sku regexp "^.{9}, [a-zA-Z0-9]{3}";
Spatie-gescheiden nummerieke ID's van 10 cijfers
# Voorbeelden: # # 0 601 122 003 # # (1) Alle string die beginnen met een cijfer ########################################################## # select * from sales_per_device_tmp where device_type regexp "^[0-9]"; # (2) Beginnend met een cijfer + spatie ########################################################## # select * from sales_per_device_tmp where device_type regexp "^[0-9] "; # (3) Cijfer + spatie + 3 cijfers ########################################################## # select * from sales_per_device_tmp where device_type regexp "^[0-9] [0-9]{3}"; # (4) Compleet ########################################################## # select * from sales_per_device_tmp where device_type regexp "^[0-9] [0-9]{3} [0-9]{3} [0-9]{3}$";