CODEWITHSUNDEEP
mysqlregex
Leveller
Leveller

Reputation: 67

Number regex search in string

I stored various numbers in a string in a MySQL database, i.e. "5,15,21". If I search for the number "5", I want the script to show me that row, but not rows that have 15 in it (when there is no "5" present). That is why I don't want to use SELECT * FROM table WHERE cell LIKE '%5%'. What reg expression can I use to target a specific number.

Upvotes: 1

Views: 43

Answers (3)

Rick James
Rick James

Reputation: 142560

mysql> SELECT FIND_IN_SET('5', '1,5,15'), FIND_IN_SET('5', '15,25');
+----------------------------+---------------------------+
| FIND_IN_SET('5', '1,5,15') | FIND_IN_SET('5', '15,25') |
+----------------------------+---------------------------+
|                          2 |                         0 |
+----------------------------+---------------------------+

Upvotes: 0

Enissay
Enissay

Reputation: 4953

Here's the alternative regex solution:

SELECT * FROM table WHERE cell RLIKE '[[:<:]]5[[:>:]]'

[[:<:]] & [[:>:]] are word boundaries, which means here that the 5 wont be matched unless it's "alone" (not part of another number) no matter it's postion

Upvotes: 1

Nir Levy
Nir Levy

Reputation: 12953

you should use the comma seperator do distinguish between 5 as a 5, and 5 as part of 15:

SELECT * FROM table WHERE concat(',' , cell, ',') LIKE '%,5,%'

The concat() is used to deal with the cases 5 is the first or last in your list

Upvotes: 2

Related Questions