Using REGEX with Sql Statement

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?

Are 3, 2 and 11 specifically the only numbers you're trying to match? — John Clifford
– John Clifford, Commented Feb 7, 2016 at 17:22
The regex for that would be the one Enissay posted. Removed mine because it matches 0-99; I missed that you didn't want 0 included. — John Clifford
– John Clifford, Commented Feb 7, 2016 at 17:25
What DB are you using? Can you show what data your currently have and what you should and shouldn't get? Maybe make a sqlfiddle? — chris85
– chris85, Commented Feb 7, 2016 at 17:28

Enissay · Accepted Answer · 2016-02-07 18:03:54Z

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

edited Feb 7, 2016 at 18:03

answered Feb 7, 2016 at 17:29

Enissay

4,9633 gold badges33 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lorenzo Over a year ago

what i'm wanting is to compare a set of numbers, the $FilterTag string will be different each time and i want to match those numbers in my database.

duduwe · Accepted Answer · 2016-02-07 17:55:43Z

0

Returns all records with 2 digits.

select * from table1
where tag REGEXP ('^[0-9]{2}$');

answered Feb 7, 2016 at 17:55

duduwe

1,32214 silver badges22 bronze badges

Collectives™ on Stack Overflow

Using REGEX with Sql Statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related