My point is that this normalization works on ALL databases. Ergo my reference to "SQL" and not "SQLite".
MySql for example has split support but it's still overkill to implement something like what you want. http://kedar.nitty-witty.com/blog/mysql-stored-procedure-split-delimited-string-into-rows All too often people smash all their data into one table and then find out things work better when normalized. Less space, faster queries, easier/faster/more accurate search (e.g. you can't index your combined field). So...normalized you can do: select * from species where name = 'g%'; Which can be indexed. With just one table you have to do: select * from species where name = '%/g%'; Which is a full table lookup. And then hope nobody puts a slash in their name for some reason which would mess up your search. The general rule is...if you are repeating values you probably need to normalize. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Jean-Denis MUYS [jdm...@kleegroup.com] Sent: Monday, September 12, 2011 7:29 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] splitting a line to produce several lines? On 12 sept. 2011, at 13:26, Black, Michael (IS) wrote: Since SQL is designed to return rows I believe the answer is "no" since you're data is not stored in rows. I suspected such a limitation. Thanks for the confirmation. (though I question the justification: "SQL is designed to return rows" is precisely the reason why one could hope it could "return rows from the results of a [split] function"). You need to do what's referred to as "normalizing" your data. That's one goal of the migration. Back to programming. JD […] Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org<mailto:sqlite-users-boun...@sqlite.org> [sqlite-users-boun...@sqlite.org<mailto:sqlite-users-boun...@sqlite.org>] on behalf of Jean-Denis MUYS [jdm...@kleegroup.com<mailto:jdm...@kleegroup.com>] Sent: Monday, September 12, 2011 3:28 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] splitting a line to produce several lines? I am migrating data from a database which has a table that describes items by "kinds". Instances of those "kinds" are stored in one column of this table, listing each instance's id, with all ids separated by a slash "/". Of course, this is a very poor design. The target system has a proper table for instances, with a one-to-many relationship between kinds and instances. My question is: is there a pure SQL way to split the instances string, and generate the instance lines from the instance list string? contrived example: Table Kind: id name instances 1 Bird 'eagle/seagull/hen' 2 Mammal 'dog/cat/cow/rabbit' 3 Fish 'tuna/shark/cod' Possible outcome: Table Instances: id name kind_id 1 eagle 1 2 seagull 1 3 hen 1 4 dog 2 5 cat 2 6 cow 2 7 rabbit 2 8 tuna 3 9 shark 3 10 cod 3 (the instance id is not especially important. it could as well be the concatenation of the kind id/name and the instance name) Of course, I could write a program to do that, but if I could do it in a few SQL statements, it would happen earlier… Thanks, Jean-Denis _______________________________________________ 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
