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
