user1858234 user1858234 - 17 days ago 7
MySQL Question

mySql regular expression in WHERE statement

I have a database where I store a code which is an implode("|", $array) of various codes. So the results will be something like:


  • 100|5100|510

  • 100|5200|510

  • 410|5200|520

  • 100|790|5100|320



I want to write a regular expression to search mySQL for matches from PHP ... I might have something like 100|*|510 to show me both 100|5100|510 and 100|5200|510. This RegEx works in PHP:

/100\|(?:.*)\|510/

But it does not work in mySQL. I found answers that ?: does not work, so if I remove that and use

/100\|.*\|510/

i.e. Query is:
SELECT * FROM tra_amounts WHERE coa_codes REGEXP "/100\|.*\|510/"

It shows all results from the table.

How do I write a RegEx to match some parts of the code, while leaving other parts of the code as a wildcard?

Thanks!

Answer

Have you heard of MySQL's LIKE operator? Something like this might be what you have in mind:

SELECT *
FROM yourTable
WHERE someCol LIKE '100|%|510;

Assuming that someCol had 100|5100|510 and 100|5200|510 as data, this query would return both of these records.