SQL IN() function

Bob Sneidar bobsneidar at iotecdigital.com
Fri May 22 16:13:41 EDT 2015


varchar (stupid spell correct)

> On May 22, 2015, at 13:13 , Bob Sneidar <bobsneidar at iotecdigital.com> wrote:
> 
> Because it is a list of integers, which is a string. I suppose I could have used archer, but when I am uncertain how many values might end up in the column, I always go with some kind of blob to be safe. 
> 
> Bob S
> 
> 
>> On May 22, 2015, at 01:26 , Peter Haworth <pete at lcsql.com> wrote:
>> 
>> IN works fine in sqlite to find any item in the supplied list. Having said
>> that, I've never used it with a BLOB type. Can't tell from your post but is
>> there a reason you're using BLOB rather than INTEGER?
>> 
>> How is your devicelist formatted?
>> 
>> Pete
>> lcSQL Software
>> On May 21, 2015 10:10 AM, "Bob Sneidar" <bobsneidar at iotecdigital.com> wrote:
>> 
>>> Okay, once again it’s Regex to the rescue.
>>> 
>>> There is no function in sqLite to do this sort of thing. Instead one can
>>> use a series of LIKE statements like so:
>>> 
>>> SELECT * FROM formsgen.service where devicelist LIKE '6480,%'
>>>       OR devicelist LIKE '%,6480'
>>>       OR devicelist LIKE '%,6480,%'
>>>       OR devicelist = ‘6480'
>>> 
>>> this covers first, last, middle and only conditions
>>> Bob S
>>> 
>>> 
>>>> On May 21, 2015, at 09:14 , Bob Sneidar <bobsneidar at iotecdigital.com>
>>> wrote:
>>>> 
>>>> Hi all.
>>>> 
>>>> I am having a problem with a query. I have the value “647,6480” in a
>>> column called devicelist. I use the query:
>>>> 
>>>> SELECT * FROM formsgen.service where 647 in(device list);
>>>> 
>>>> It returns one record as it should. However if I use the query:
>>>> 
>>>> SELECT * FROM formsgen.service where 6480 in(device list);
>>>> 
>>>> I get NO RECORDS. Am I misunderstanding the IN function? BTW the column
>>> is a BLOB type. Not sure if that matters.
>>>> 
>>>> Bob S
>>>> 
>>>> 
>>>> _______________________________________________
>>>> use-livecode mailing list
>>>> use-livecode at lists.runrev.com
>>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>> 
>>> _______________________________________________
>>> use-livecode mailing list
>>> use-livecode at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> 



More information about the use-livecode mailing list