On Wed, 18 Jun 2008, P Kishor wrote: >> CREATE TABLE foo (ID INTEGER PRIMARY KEY, ITEMS); >> INSERT INTO foo VALUES(1,item1;item2;item3); >> INSERT INTO foo VALUES(2,item1;item4); >> INSERT INTO foo VALUES(3,item5;item3;item7); > > The above is incorrect SQL. If you run the above INSERT commands, you > will get an error because the ITEMS values should be quoted with > single quotes. It really should be > > INSERT INTO foo VALUES (1, 'item1;item2;item3');
Sorry, I was just using this for illustration of the data structure, I should have shown it with the (implied) quotes. > Write a custom function. This kind of stuff is trivial to do in a > programming language, but SQL is not really the best for this. If you > are getting the SQL statements to construct your db, best is to > preprocess that with Perl or Python or whatever you desire. I am not getting the SQL statements, I am getting the sqlite database already in the format shown. I can definitely re-shape it via external functions/processing, but I wondered if anyone had any SQL-based solutions. I am often pleasantly surprised to find that SQL-syntax exists for doing something I didn't think SQL was capable of. Chris On Wed, 18 Jun 2008, P Kishor wrote: > On 6/17/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> I have a table that contains 2 fields: ID, ITEMS, as: >> >> CREATE TABLE foo (ID INTEGER PRIMARY KEY, ITEMS); >> INSERT INTO foo VALUES(1,item1;item2;item3); >> INSERT INTO foo VALUES(2,item1;item4); >> INSERT INTO foo VALUES(3,item5;item3;item7); >> > > The above is incorrect SQL. If you run the above INSERT commands, you > will get an error because the ITEMS values should be quoted with > single quotes. It really should be > > INSERT INTO foo VALUES (1, 'item1;item2;item3'); > >> As shown, ITEMS is comprised of individual data items separated by >> semicolons. For some purposes, this concatenated form suits the purpose, >> but for other purposes I need to split these items into individual data >> points. That is, I want to split the ITEMS (at the semicolons) into >> separate rows in a new table FOO2, having fields ID and SINGLEITEM that >> looks like this: >> >> ID, SINGLEITEM >> 1,item1 >> 1,item2 >> 1,item3 >> 2,item1 >> 2,item4 >> 3,item5 >> 3,item3 >> 3,item7 >> >> Any suggestions of how to do this in SQLite? >> > > > Write a custom function. This kind of stuff is trivial to do in a > programming language, but SQL is not really the best for this. If you > are getting the SQL statements to construct your db, best is to > preprocess that with Perl or Python or whatever you desire. > > -- > Puneet Kishor > _______________________________________________ > 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
