CODEWITHSUNDEEP
mysqlsqlsql-like
doub1ejack
doub1ejack

Reputation: 11181

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    |
---+---------------+---

Upvotes: 3

Views: 3293

Answers (2)

anubhava
anubhava

Reputation: 786329

You may use this select query using REGEXP:

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

RegEx Details:

  • [[:<:]][0-9]+[[:>:]]: Match 1+ digits surrounded with word boundary assertions

Upvotes: 4

user unknown
user unknown

Reputation: 36269

Something like that:

WHERE ids LIKE '%,16,%' OR ids LIKE '%,16' OR ids LIKE '16,%';

Postgresql even has pattern matching - I don't know for mysql: 

WHERE ids ~ '^(.*,)?16(,.*)?$';

Upvotes: 2

Related Questions