I would like a MySQL regexp pattern that looks at comma separated names and results in 1/True if all the names have to deal with Covid labs, but 0/False if they do not. Some examples below with expected behavior:
SET @pattern = 'some regexp pattern'
-- example 1:
SELECT 'COVID-19 Positive Lab Results' REGEXP @pattern;
>>> 1
--example 2:
SELECT 'COVID-19 Positive Lab Results, Traveling Test, COVID-19 Pending Lab Results REGEXP @pattern;
>>> 0
--example 3:
SELECT 'COVID-19 Positive Lab Results, COVID-19 Pending Lab Result - ABC'
REGEXP @pattern;
>>> 1
The pattern I have so far is '(covid.*[lab|test].*,)*.*covid.*[lab|test]([^,]) $'
My thinking was that (covid.*[lab|test].*,)*
would find 0 or any uninterrupted number of entries that have to deal with covid lab/test results and then I would require it with .*covid.*[lab|test]([^,]) $
to end with a Covid lab result pattern. Neither part is working right now:
the first one returns 1 for:
'COVID-19 Positive Lab Results, Traveling Test, COVID-19 Pending Lab Results, COVID-19 test'
and the second one returns 1 for:
'COVID-19 Positive Lab Results, ab'
Solution doesn't have to be regex to be clear
CodePudding user response:
You can use
^[^,]*covid[^,]*(lab|test)[^,]*(,[^,]*covid[^,]*(lab|test)[^,]*)*$
Details
^
- start of string[^,]*covid[^,]*(lab|test)[^,]*
- zero or more chars other than a comma,covid
substring, zero or more chars other than a comma,lab
ortest
, zero or more chars other than a comma(,[^,]*covid[^,]*(lab|test)[^,]*)*
- zero or more sequences of a comma, zero or more chars other than a comma,covid
substring, zero or more chars other than a comma,lab
ortest
, zero or more chars other than a comma$
- end of string.
One of the problems was the fact that you used a bracket expression ([...]
) instead of a grouping construct ((...)
).