On 05/13/2011 03:15 PM, Trevor Borgmeier wrote:
> I have a database where a field's value is a comma separated list
> indicating the basic categories the item belongs to.
>
> so if the "categories" field has a value of "1,8,15"
>
> I want to do a query like this:
>
> SELECT categories FROM myTable WHERE "8" IN (categories);
>
> but it only finds records where "8" is the only category...
>
> Is there anyway for it to evaluate the contents fo the categories field
> first rather than compare it as a whole?
>
> The describe query works in MySQL, but the port doesn't... So far the
> hack is to do something like this...
>
> SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%";
>
> but I'm guessing LIKE isn't as efficient, and the query is more
> awkward. Any advise would be appreciated. Thanks!
>
> -Trevor
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Careful with how you have your like. As an added bonus, if you were to
hunt for category 1, your statement would be LIKE "%,1,%" which would
return no results.
If you're importing the data, I'd suggest, as others have, to massage
the data and create the additional table.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
