mysqlsqlsql-like

sql to match number in string

I have a table that has a comma separated list of ids in one of the fields (not my doing). I want to know if I can use LIKE to match a number in this string? The problem is I don't want to get similar numbers. Is there a way to match a number with no numeric charcters on either side?

SELECT * FROM table WHERE activitiesids LIKE 6

   | activitiesids |
---+---------------+---
   |   3,16,8,6    |
---+---------------+---
   |       6       |
---+---------------+---
   |      7,560    |
---+---------------+---
Solution

  • You may use this select query using REGEXP:

    SELECT * FROM table WHERE activitiesids REGEXP '[[:<:]][0-9]+[[:>:]]';

    RegEx Details: