In a text column (HTML) are used placeholders:
[[ID|STRING]]
Examples:
[[2|telefon]]
[[43|name]]
And there are placeholders using the following format:
[[ID|STRING|STRING]]
Examples:
[[56|baustein|s]]
The first string is always named "baustein", the second one is alwys "s" or "d".
I want to get all records where the string column contains the first kind of placeholder but not the second.
My regular expression is:
\[\[#d.id#\|(anrede|name1|name2|name3|strasse|plz|ort|telefon|fax|email|emailaddress|web|bild)\]\]
Notice: #d.id# is a variable containing an integer.
Unfortunately the result of the query includes all records containing the first AND the second placeholder instead of the first one only.
I have tested the regexp using https://regex101.com/ (PCRE2 PHP) and it seems to be ok.
What am I missing in conjunction with MySQL?
CodePudding user response:
Use
REGEXP '[[][[][0-9] [|](anrede|name1?|name2|name3|strasse|plz|ort|telefon|fax|email|emailaddress|web|bild)]]'
EXPLANATION
--------------------------------------------------------------------------------
[[] any character of: '['
--------------------------------------------------------------------------------
[[] any character of: '['
--------------------------------------------------------------------------------
[0-9] any character of: '0' to '9' (1 or more
times (matching the most amount possible))
--------------------------------------------------------------------------------
[|] any character of: '|'
--------------------------------------------------------------------------------
( group and capture to \1:
--------------------------------------------------------------------------------
anrede 'anrede'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
name 'name'
--------------------------------------------------------------------------------
1? '1' (optional (matching the most amount
possible))
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
name2 'name2'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
name3 'name3'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
strasse 'strasse'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
plz 'plz'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
ort 'ort'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
telefon 'telefon'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
fax 'fax'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
email 'email'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
emailaddress 'emailaddress'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
web 'web'
--------------------------------------------------------------------------------
| OR
--------------------------------------------------------------------------------
bild 'bild'
--------------------------------------------------------------------------------
) end of \1
--------------------------------------------------------------------------------
]] ']]'
DO NOT escape ]
brackets, escape single characters with character classes, or use doubled backslashes, \\[\\[
and \\|
.