Re: [sqlite] Using a customized collate function to mimic integer array type.

Even if SQLite was already implementing a bigger subset of the array type of SQL2003 standard, your users would still have a few more words to learn : 'ROW', 'MEMBER OF', 'UNNEST' "C > 100 (match 158)" => " max(UNNEST(C)) > 100 " "C < 100 (match 42 and 76)" => " min(UNNEST(C)) < 100 " "C =

Re: [sqlite] Using a customized collate function to mimic integer array type.

On Fri, 21 Mar 2014 09:35:19 -0500 Ben Peng 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

Re: [sqlite] Using a customized collate function to mimic integer array type.

On Fri, Mar 21, 2014 at 8:06 PM, Ben Peng wrote: > > I guess I will have to take the longer route, namely define a customized > comparison function and translate user input internally. > There's an also virtual table method, probably not so easy to wrap the head around, but

Re: [sqlite] Using a customized collate function to mimic integer array type.

Thanks Dan, I think I get your point. COLLATE not only provides a new way to compare values (what is what I need), but also gives new appearances to existing values by which they are sorted or grouped. So 158;42;76 can not appear as 158, 42, and 76 at the same time (158;42;76 == 42, 158;42;76 ==

Re: [sqlite] Using a customized collate function to mimic integer array type.

On 03/21/2014 10:33 PM, Ben Peng wrote: 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

Re: [sqlite] Using a customized collate function to mimic integer array type.

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

Re: [sqlite] Using a customized collate function to mimic integer array type.

On Fri, 2014-03-21 at 09:35 -0500, Ben Peng wrote: > Dear sqlite experts, I'm far from an "sqlite expert", others should be able to provide a more authoritive answer I think what you want is rather to simply define your own custom function to implement a custom match. I think using COLLATE is

[sqlite] Using a customized collate function to mimic integer array type.

Dear sqlite experts, 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