How do I select rows where a column contains

here is the field in my table

how do I select rows where a column contains only 5, not 15 if SELECT * FROMvendorWHEREservicesLIKE '%5%' it will select that have 15开发者_运维技巧 too. any idea?

---

As other comments noted, normalize your database. But, here's a hack to get what you're looking for temporarily:

select * from vendor where ',' + services + ',' like '%,5,%'

---

select * from vendor where services = '5' OR services LIKE '5,%' OR services like '%,5' OR services LIKE '%,5,%'

But seriously normalize the DB

---

You can use regexp:

select * from my_table
    where 
    my_col regexp '^5$' or
    my_col regexp ',5$' or
    my_col regexp ',5,';

Haven't tried the above expression myself, but something like that would work.

EDIT: with one regexp:

select * from my_table
    where 
    my_col regexp '^5$|,5$|,5,'

---

Actually MySQL provides a FIND_IN_SET function for such comma-separated strings. This is simpler and cleaner than complex like or regexp solutions:

mysql> select * from vendor where find_in_set('5', services) > 0;
+----+----------+
| id | services |
+----+----------+
|  1 | 5        |
|  3 | 9,5      |
+----+----------+
2 rows in set (0.00 sec)

But still beware - such design could result in low performance.

---

Using regexp:

SELECT * FROM `VENDOR` WHERE `services` REGEXP '[[:<:]]5[[:>:]]';

(I just recently answered this in Match tags in MYSQL)

---

What you can do is in where condition you can write services='5' or services like '%,5' or services like '%,5,%' or services like '5,%'