The vtable split method will happily accept a field from a join as in Select t.key,c.value from table t cross join cmlist on c.commalist=t.field;
Virtual tables don't declare virtual indices; they return an index number and an index string from their BestIndex method. -----Ursprüngliche Nachricht----- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Samstag, 05. April 2014 10:24 An: General Discussion of SQLite Database Betreff: Re: [sqlite] comma-separated string data On Saturday, April 5, 2014, Max Vlasov <max.vla...@gmail.com> wrote: > On Fri, Apr 4, 2014 at 10:20 PM, peter korinis > <kori...@earthlink.net<javascript:;>> > wrote: > > A data column in a link table contains comma-separated string data, > > where > > > > How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to > > extract these values and use them in an SQL statement, perhaps a WHERE > > id='66'? > > In similar cases I use my virtual table explained here: > http://www.mail-archive.com/sqlite-users@sqlite.org/msg63453.html > Actually the table works more like "function", so only one instance is > required in the db to apply this trick. > [...] > This trick successfully works with joins and everything else. > I don't think it works in this case Max, because your technique relies on the where clause being a literal, whereas here, if I'm reading between the lines correctly, the poster wants the the equivalent of Oracle's TABLE() operator. In this case, a vtable can still help, but one specific to the source table, with only the source table's PK columns plus the one to "un-nest" / parse. Basically xNext behaves like a compound iterator, with the outer iterator scanning the source table (using normal SQL and the SQLite API), and the inner iterator returning the CSV values one at a time of the current outer iterator's value / row. That's basically normalizing on the fly. The vtable should ALSO declare an index on the PK columns to avoid full scans with a where clause or a join. --DD _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ----------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
