3

I am having a table with a column that has few ids that were put into database with multi select. Column for example contains: 1,4,5,7,9. Is it possible to check if this column contains for example number 5 or not in it through MySQL query ?. I need to select all the people that have number 5 or some other listed in that field and print them through php.

AeroX
  • 3,387
  • 2
  • 25
  • 39
Vuk Vasić
  • 1,398
  • 10
  • 27
  • 2
    It's definitely possible. You can do it with a LIKE statement; though it would be easier for you if your database design was normalised. – andrewsi Jul 19 '13 at 16:14

4 Answers4

11

http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_find-in-set

SELECT ...
WHERE FIND_IN_SET(5, list_column)

But understand that this search is bound to be very slow. It cannot use an index, and it will cause a full table-scan (reading every row in the table). As the table grows, the query will become unusably slow.

Please read my answer to Is storing a delimited list in a database column really that bad?

You can use @MikeChristensen's answer to be more standard. Another trick with standard SQL is this:

select * from TableName
where ',' || ids || ',' LIKE '%,5,%'

(in standard SQL, || is the string concatenation operator, but in MySQL, you have to SET SQL_MODE=PIPES_AS_CONCAT or SET SQL_MODE=ANSI to get that behavior.)

Another MySQL-specific solution is to use a special word-boundary regular expression, which will match either the comma punctuation or beginning/end of string:

select * from TableName
where ids RLIKE '[[:<:]]5[[:>:]]'

None of these solutions scale well; they all cause table-scans. Sorry I understand you cannot change the database design, but if your project next requires to make the query faster, you can tell them it's not possible without redesigning the table.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • i know about normal forms :) and i know that this is bad. But the system is already created by someone else and i can't change it. Belive me i would make database normal if i could :D. Thanks this function is good :). I would accept this answer in 10 minutes :) – Vuk Vasić Jul 19 '13 at 16:20
  • `+1` for the built-in `mySql` function.. I'll leave my answer up since it's more *standard*.. – Mike Christensen Jul 19 '13 at 16:23
  • Super answer and worked perfectly. Voted. Thank you ! – Sumith Harshan Oct 04 '14 at 19:38
6

Perhaps:

select * from TableName
where ids = '5'     -- only 5
or ids like '5,%'   -- begins with 5
or ids like '%,5'   -- ends with 5
or ids like '%,5,%' -- 5 in the middle somewhere

It probably won't be very fast on large amounts of data. I'd suggest normalizing these multi-selection values into a new table, where each selection is a single row with a link to TableName.

Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
3
select * from your_table where concat(',',target_column,',') like '%,5,%'
Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
Eric Jo
  • 31
  • 2
0

you can write the sql query like this, for example you are looking for the number 5 select * from your_table_name where ids='5' if you want to check the result with php just tell me i will write it for you :)

Walid Naceri
  • 160
  • 5
  • That will work if it's the only entry in the field; if the field is '1,5,9', it won't find it. – andrewsi Jul 19 '13 at 16:17
  • yeah, :( sorry my english is abd now i understood the question well if the filed for example contain 1,5,9,10,3 and you want to check if the field contain 10 just write select * from your_table where ids like '%5%'; – Walid Naceri Jul 19 '13 at 16:20
  • Ah, not quite - that would wrongly return people with the value 15. – andrewsi Jul 19 '13 at 16:21