CODEWITHSUNDEEP
mysqlsqldatabaseselect
user759235
user759235

Reputation: 2207

check if value is present in one of the database rows

Im looking for a way to check if a value is present in one of the rows of the page column.

For example if should check if the value '45' is present?

 Id | page     |
 ---------------
 1  | 23       |
 ---------------
 2  |          |
 ---------------
 3  | 33,45,55 |
 ---------------
 4  | 45       |
 ---------------

Upvotes: 4

Views: 168

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You should not store values in lists. This is especially true in this case:

  • Values should be stored in the proper data type. You are storing numbers as characters.
  • Foreign key relationships should be properly defined.
  • SQL doesn't have very good string processing functions.
  • Resulting queries cannot make use of indexes.
  • SQL has a great data type for lists, called a table. In this case, you want a junction table.

Sometimes, you are stuck with other people's really bad design decisions. In that case, you can use find_in_set() as suggested by Mureinik.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311308

The find_in_set function is just what you're looking for:

SELECT *
FROM   mytable
WHERE  FIND_IN_SET('45', page) > 0

Upvotes: 2

Related Questions