CODEWITHSUNDEEP
phpmysqlsqldatabase
Vuk Vasić
Vuk Vasić

Reputation: 1418

Check if mysql field contains a certain number in mysql query

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.

Upvotes: 3

Views: 7628

Answers (4)

Eric Jo
Eric Jo

Reputation: 31

select * from your_table where concat(',',target_column,',') like '%,5,%'

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562951

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.

Upvotes: 11

Walid Naceri
Walid Naceri

Reputation: 160

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 :)

Upvotes: 0

Mike Christensen
Mike Christensen

Reputation: 91724

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.

Upvotes: 6

Related Questions