0

I've got a field in my database which contains comma-separated integers, for example:

"4,6,18,26,29,34"

I need to construct an SQL query which will return the record which contains a specific given integer, for example 6, so my current query is like this:

SELECT * FROM mytable WHERE CSVField LIKE "%,6,%"

I've surrounded the desired value with commas to avoid 6 matching 26 however it's obvious that my current query won't match against the first or last values in the field because the field doesn't start or end with a comma, so in the example above it'll never find 4 or 34.

How can I write my query so that it'll do what I want?

Andy Groom
  • 619
  • 1
  • 7
  • 15

1 Answers1

0

You can try:

SELECT * FROM mytable WHERE CSVField LIKE "%,6,%"
                         OR CSVField LIKE "6,%"
                         OR CSVField LIKE "%,6"

Hope you table is quite small though.

UPDATE

Following Mihai's post, try:

SELECT * FROM mytable WHERE FIND_IN_SET(CSVField,'6') > 0
FDavidov
  • 3,505
  • 6
  • 23
  • 59