CODEWITHSUNDEEP
mysqlregex
fyn
fyn

Reputation: 52

How to match hyphen delimited in any order

I need to match a set of characters delimited by a hyphen - for example:

B-B/w-W/Br-W-Br

Where the / are part of what I need, up to 20 spaces.

G-R-B, G/R-B-B/W-O

So I need a regex that covers between the -'s in any order (G-R-B could also be R-B-G) I've been playing around with a bunch of combo's, but I can't come up with something that will match any order.

The plan is to search this way using mysql. So, it'll be something like

select * from table1 where pinout REGEXP '';

I just can't get the regex right :/

Upvotes: 1

Views: 152

Answers (2)

Ro Yo Mi
Ro Yo Mi

Reputation: 15000

Description

This expression will match the string providing each of the hyphen delimited values are included in the string. The color values can appear in the string in any order so this expression will match W/Br-b-B/w and B/w-W/Br-b... or any other combinations which include those colors.

^                 # match the start to of the string
(?=.*?(?:^|-)W\/Br(?=-|$))    # require the string to have a w/br
(?=.*?(?:^|-)b(?=-|$))        # require the string to have a b
(?=.*?(?:^|-)B\/w(?=-|$))     # require the string to have a b/w
.*                # match the entire string

enter image description here

MySql doesn't really support the look arounds so this will need to be broken into a group of where statements

mysql> SELECT * FROM dog WHERE ( color REGEXP '.*(^|-)W\/Br(-|$)' and color REGEXP '.*(^|-)b(-|$)' and color REGEXP '.*(^|-)B\/w(-|$)' );

+-------+--------+---------+------+------------+---------------------+
| name  | owner  | species | sex  | birth      | color               |
+-------+--------+---------+------+------------+---------------------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | B-B/w-W/Br-W-Br     |
| Buffy | Harold | dog     | f    | 1989-05-13 | G-R-B, G/R-B-B/W-O  |
+-------+--------+---------+------+------------+---------------------+

See also this working sqlfiddle: http://sqlfiddle.com/#!2/943af/1/0

Using a regex in conjunction with a MySql where statement can be found here: http://dev.mysql.com/doc/refman/5.1/en/pattern-matching.html

Upvotes: 1

Chris
Chris

Reputation: 8656

I might have misunderstood from your example, try this:

-*([a-zA-Z/]+)-*

The capture region can be altered to include your specific letters of interest, e.g. [GRBWOgrbwo/].

Edit: I don't think this will help you in the context you're using it, but I'll leave it here for posterity.

Upvotes: 0

Related Questions