Hi, Tristan,

Your solution definitely works (we have defined a few custom functions) but
our application hides databases from users but allows users to use simple
conditions to retrieve results. To use this function, we would have to

1. teach users use this function, which is hard to do because it is
application specific and they need to know which fields need to use this
function.

2. translate user input to use this function internally. We will need to
use a separate table to record the fields that need translation, and change
user input accordingly.

A COLLATE function seems to mark the columns directly and save us from the
trouble of translating user input, so it might be a better solution in this
case.

Thanks,
Bo



On Fri, Mar 21, 2014 at 10:05 AM, Tristan Van Berkom <
tris...@upstairslabs.com> wrote:

> On Fri, 2014-03-21 at 09:35 -0500, Ben Peng wrote:
> > Dear sqlite experts,
>
> <disclaimer>I'm far from an "sqlite expert", others should be able to
> provide a more authoritive answer</disclaimer>
>
> I think what you want is rather to simply define your own custom
> function to implement a custom match.
>
> I think using COLLATE is wrong in this case, because the use case of
> COLLATE is generally to implement ORDER BY, or perhaps to build a custom
> index (to replace the regular strcmp() like behavior) but what you want
> is a rather loose matching algorithm.
>
> If you create a function using sqlite3_create_function(), similar to how
> you might define a REGEX function, you might execute a query with:
>
>   SELECT * FROM table WHERE custom_match (table.column, 42) = 0;
>
> And then, lets assume that "custom_match" uses sqlite3_result_int()
> to set the result to 0 if there is a match, or -1 if there is no match,
> then you should get a match for any row where table.column = 158;42;76
>
> You can of course give more parameters to "custom_match", so that you
> can use invocations like this:
>   custom_match (GREATER, table.column, 42)
>   custom_match (EQUALS, table.column, 42)
>   custom_match (LESSER, table.column, 42)
>
> Make sense ?
>
> Cheers,
>     -Tristan
>
> >
> > 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. For
> > example, the record could be selected by either
> >
> > C > 100 (match 158)
> > C < 100 (match 42 and 76)
> > C = 42 (match 42)
> >
> > From what I have read so far, I could define a special collate function
> for
> > these columns (sqlite3_create_collation etc), but before I jump into the
> > details, does anyone know
> >
> > 1. Is this the correct way to proceed?
> > 2. Has anyone done anything similar so that I do not have to reinvent the
> > wheel?
> >
> > Thank you very much,
> > Bo
> > _______________________________________________
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to