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';

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}$";

Zie ook

• Regular expressions (algemeen)

Bronnen

• https://dev.mysql.com/doc/refman/5.1/en/pattern-matching.html
• https://dev.mysql.com/doc/refman/5.1/en/regexp.html
• https://stackoverflow.com/questions/15687136/logical-and-operator-in-mysql-regexp