将REGEX与Sql语句一起使用

Question

I'm wanting to check if in my column named "tag" it contains numbers with two digit numbers using Regex.

my query:

$FilterTag="3,2,11"
$sql = "SELECT * FROM table1 WHERE bTown = ? AND REGEXP_LIKE(tag, '.*[$FilterTag]')";

My table: db table tag

the only problem is that my query is finding single character numbers ,like instead of telling me that it contains 11 it will tell me that it contains 1.

Is there any way that i can group a character set of numbers?

Answer 1

Your question was not clear!

So you want to get entries with tag having 3 AND 2 AND 11.

You'll have to split your string/filter 3,2,11, then generate the condition using a loop:

$sql = "SELECT * FROM table1 WHERE bTown = ?";

foreach (explode(",", $FilterTag) as $value)
    $sql .= " AND REGEXP_LIKE(tag, '\b$value\b')";

which should produce the following query:

SELECT * FROM table1 WHERE bTown = ? 
    AND REGEXP_LIKE(tag, '\b3\b')
    AND REGEXP_LIKE(tag, '\b2\b')
    AND REGEXP_LIKE(tag, '\b11\b')

Extras:

To allow only numbers from 1 to 99, use this regex:

\b[1-9][0-9]?\b

DEMO

To allow two digits numbers (with leading zeros for numbers <10) use:

\b(0[1-9]|[1-9][0-9])\b

DEMO