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
