Re: [sqlite] splitting a line to produce several lines?

[Black, Michael (IS)]

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  nameinstances
1   Bird'eagle/seagull/hen'
2   Mammal  'dog/cat/cow/rabbit'
3   Fish'tuna/shark/cod'

Possible outcome:

Table Instances:

id  namekind_id
1   eagle   1
2   seagull 1
3   hen 1
4   dog 2
5   cat 2
6   cow 2
7   rabbit  2
8   tuna3
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

Re: [sqlite] splitting a line to produce several lines?

[Max Vlasov]

On Mon, Sep 12, 2011 at 12:28 PM, Jean-Denis MUYS  wrote:
>
> 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:
>

This kind of questions appear from time to time (including my brain :)
and there are tricky solutions like for example using specially
implemented virtual tables (read my recent post here:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg63453.html)

Thanks to your example I added delimiter support in the virtual table
initialization and tested the concept on a real data, seems like it
works. The query in this case looks like this

SELECT TestTable.Id, TestTable.Name, cmlist.value FROM TestTable LEFT
JOIN cmlist On Instances=commalist

For those who familiar with sqlite virtual tables implementation this
concept is probably an hour to implement. If you're on windows and
ready to test your conversion with my tool, consider writing to me
directly

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] splitting a line to produce several lines?

[Jean-Denis MUYS]

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  nameinstances
1   Bird'eagle/seagull/hen'
2   Mammal  'dog/cat/cow/rabbit'
3   Fish'tuna/shark/cod'

Possible outcome:

Table Instances:

id  namekind_id
1   eagle   1
2   seagull 1
3   hen 1
4   dog 2
5   cat 2
6   cow 2
7   rabbit  2
8   tuna3
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

Re: [sqlite] splitting a line to produce several lines?

[Black, Michael (IS)]

Since SQL is designed to return rows I believe the answer is "no" since you're 
data is not stored in rows.

You need to do what's referred to as "normalizing" your data.
If you normalize the answer becomes a natural query.

create table kind (kind_id int primary key, name text);
insert into kind values(1,'Bird');
insert into kind values(2,'Mammal');
insert into kind values(3,'Fish');
create table species (species_id int primary key, name text,kind_id int);
insert into species values(1,'eagle',(select kind_id from kind where 
name='Bird'));
insert into species values(2,'seagull',(select kind_id from kind where 
name='Bird'));
insert into species values(3,'hen',(select kind_id from kind where 
name='Bird'));
insert into species values(4,'dog',(select kind_id from kind where 
name='Mammal'));
insert into species values(5,'cat',(select kind_id from kind where 
name='Mammal'));
insert into species values(6,'cow',(select kind_id from kind where 
name='Mammal'));
insert into species values(7,'rabbit',(select kind_id from kind where 
name='Mammal'));
insert into species values(8,'tuna',(select kind_id from kind where 
name='Fish'));
insert into species values(9,'shark',(select kind_id from kind where 
name='Fish'));
insert into species values(10,'cod',(select kind_id from kind where 
name='Fish'));
sqlite> select species_id,species.name,kind.name from species join kind on 
species.kind_id=kind.kind_id;
1|eagle|Bird
2|seagull|Bird
3|hen|Bird
4|dog|Mammal
5|cat|Mammal
6|cow|Mammal
7|rabbit|Mammal
8|tuna|Fish
9|shark|Fish
10|cod|Fish

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 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  nameinstances
1   Bird'eagle/seagull/hen'
2   Mammal  'dog/cat/cow/rabbit'
3   Fish'tuna/shark/cod'

Possible outcome:

Table Instances:

id  namekind_id
1   eagle   1
2   seagull 1
3   hen 1
4   dog 2
5   cat 2
6   cow 2
7   rabbit  2
8   tuna3
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

[sqlite] splitting a line to produce several lines?

[Jean-Denis MUYS]

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  nameinstances
1   Bird'eagle/seagull/hen'
2   Mammal  'dog/cat/cow/rabbit'
3   Fish'tuna/shark/cod'

Possible outcome:

Table Instances:

id  namekind_id
1   eagle   1
2   seagull 1
3   hen 1
4   dog 2
5   cat 2
6   cow 2
7   rabbit  2
8   tuna3
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