CODEWITHSUNDEEP
mysqlsqldatabase
Satanand Tiwari
Satanand Tiwari

Reputation: 496

How to get record where find_in_set from a comma saperate string.[MySql]

I have a comma separate string like "1,2,3" and a column in table is also contain comma separate value like "1,2,4,5,3" how to get all records that match any value to any value.

for example

If I search for string "1,2,3" then I should get record the category contains 1 or 2 or 3 or 1,2 or 1,3 or 2,3 or 1,2,3. It should not return the duplicate value where as we can group them.

Is it possible to get all record with a single query.

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

You should not be storing lists of ids in strings. Here are reasons why:

  • Values should be stored using the correct type. int <> string.
  • SQL has lousy string processing functions.
  • Foreign keys should be properly declared.
  • SQL will not be able to optimize these queries.
  • SQL has a great way to store lists. It is called a table not a string.

But, sometimes you are stuck with someone else really, really, really, really, really bad data modeling decisions. You can do something, using regular expressions:

where category regexp replace($string, ',', '|')

or perhaps more accurately:

where concat(',', category, ',') regexp concat(',', replace($string, ',', ',|,'), ',')

Upvotes: 0

Sriram Jano
Sriram Jano

Reputation: 56

Try this:

select * from table where category IN (1,2,3);

Upvotes: 0

Related Questions