'MySQL REGEXP to find a word and then non-comma characters till end line

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

mysqlregex


Solution 1:[1]

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 or test, 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 or test, 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 ((...)).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Wiktor Stribiżew

Related Questions