CODEWITHSUNDEEP
sql
Crystal
Crystal

Reputation: 29508

Query data from sql database with "," separator

I'm pretty new to SQL and looking at some code. I can see the basic structure for getting items out of a Table where column="Apples" or something like that. 

select * from fruitsTable where fruit="apple"

If the data can be 

apple
orange
apple,orange
pear
apple,pear

Is there a way to make that query? I know that apple,orange and apple,pear don't really make sense, but basically I'm trying to filter out the data from a database and sometimes it's just one, and other times it's two categories. How would I filter/query the database for something like that? Thanks.

Upvotes: 2

Views: 6927

Answers (2)

Mark Byers
Mark Byers

Reputation: 838696

If you are using MySQL you can use WHERE FIND_IN_SET('apple', fruit).

For other databases you can use WHERE (',' || fruit || ',') LIKE '%,apple,%'.

In both cases the query can't use an index and it will be slow if your table is large.

Upvotes: 1

user596075
user596075

Reputation: 

select *
from fruitsTable
where fruit like '%apple%'

What that query does is return all records that have the string 'apple' somewhere in the fruit field.

N.B. please don't design a database like this. Comma separated values are a quick way to make a bad database.

Upvotes: 0

Related Questions