On Fri, 21 Mar 2014 09:35:19 -0500
Ben Peng <ben....@gmail.com> wrote:
> I have an application where data stored in columns can be lists of
> integers (e.g. 158;42;76). I cannot really split such record into
> multiple records (one for 158, one for 42 etc) and I am currently
> storing them as VARCHAR because they represent a complete piece of
> information. However, when I select records, I would like to compare
> or match any of the values.
In relational terms, your column with "158;42;76" is not in 1NF.
You want to match "any" of the values: an OR condition. That
tells you they are distinct, and should each be on their own row, viz.,
158;42;76 158
158;42;76 42
158;42;76 76
Create a table like that, and join it to the one you have.
Someone else mentioned using a VTF to accomplish that on the fly. That
would work, too. A better solution (that is not a SQLite feature yet)
would be a table-valued function.
Today, SQLite supports two kinds of functions, scalar and aggregate.
Using R for the number of rows and C for the number of columns, these
can be described as
Scalar: 1 x C -> 1 x 1 output
Aggregate: R x C -> 1 x 1 output
A table-valued function is
Table: R x C -> N x M output
which looks a lot like an ordinary join, doesn't it?
IOW, there's no fixed relationship between the number of input rows and
columns, and the number of output rows and columns. Using your
example,
Example: R x 1 -> 3R x 2
and your SQL becomes
select t.*, tf.output
from existing_table as t
join tf(t.values) as tf
on t.values = tf.values;
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
