CODEWITHSUNDEEP
mysqlregex
Fabrice Lefloch
Fabrice Lefloch

Reputation: 417

regexp match for a number between 2 patterns

I'm trying to add a regexp in my SQL query to find a specific number in a string.

The parameters of this search should be that :

I was able to write a regexp that is working fine for PHP : (?<=^|\+|\|)(2)(?=\||\+|$) But I discovered that MySQL doesn't support this kind of regexp.

Any help to convert it to a MySQL compatible regex ?

Thank you.

Upvotes: 1

Views: 70

Answers (2)

Rick James
Rick James

Reputation: 142218

Thinking out of the box...

WHERE FIND_IN_SET(2, 
   REPLACE(REPLACE(col, '+', ','), '|', ',')) )

With REGEXP_REPLACE(), it can be written simpler:

WHERE FIND_IN_SET(2, 
   REGEXP_REPLACE(col, '[+|]', ',') )

Upvotes: 0

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626738

Note that in MySQL v8+, you may use lookarounds and the

(?<=^|[+|])2(?=[|+]|$)

is valid with the ICU regex library.

However, you may simply use alternations inside groups. This pattern will work in older MySQL, too:

(^|[+|])2([|+]|$)

The pattern will return all records containing texts matching

  • (^|[+|]) - start of record, + or |
  • 2 - 2 char
  • ([|+]|$) - end of record, + or |

Upvotes: 1

Related Questions