> 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!
This LIKE is the only way to get information from your table. But you
can do it more efficiently by changing your database schema. I'd
suggest to have additional table to store all those categories
separately, one category per row (with columns like id_from_table,
category). Then you'll be able to have a simple and efficient query on
it.
Pavel
On Fri, May 13, 2011 at 3:15 PM, Trevor Borgmeier <tre...@climbd.com> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
