CODEWITHSUNDEEP
mysqlregex
trevrobwhite
trevrobwhite

Reputation: 453

MySQL REGEXP Match / or End of Field

I've got a load of paths in a MySQL Database (5.7.17-0ubuntu0.16.04.2) and the user chooses a selection of paths, I want to select all those paths and those below, but I've hit an issue.

Say the user wants "/root/K" I need to do a select for:

a. /root/K%
b. /root/K

How can I get the REGEXP to match the end of a field or a / ?

I've tried the following:

original query:

where path REGEXP ('/root/K/|/root/J/J/')  # this works but doesn't show the items in that path, only ones below

where path REGEXP '/root/K[/\z]' # does the same as above
where path REGEXP '/root/K(?=/|$)'   # Get error 1139, repetition-operator invalid

I've also tried: Regex to match _ or end of string but that gives error 1139

Any other suggestions?

Upvotes: 1

Views: 362

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627082

There is no support for lookarounds, not \z anchor in MySQL regex. You may use a normal capturing group:

WHERE path REGEXP '/root/K(/|$)'

It will match

  • /root/K - literal char sequence
  • (/|$) - either / or end of entry.

Upvotes: 1

Related Questions