Simple Search using LIKE or something else

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Simple Search using LIKE or something else

Stephen Chrzanowski
I've written Win32 desktop alarm clock that can store unlimited number
of alarms (Pending drive space, of course).  The problem I'm having is
that right now, I've got about 20 alarms for things I need to do over
the next couple of weeks that its getting harder to find what I need
to update if needed (Changing schedules, text, etc).

I've added a simple filter function that uses SQLites LIKE operator,
but I'd like something a little bit more advanced.  Right now, the
SQL code is like:

select * from Events where Title like '%Abc%Def%'

This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.

I've thought about doing some kind of delimiter, then have the code
generate the SQL code by just looping through the keywords and
generate the "or Title like '%keyword%'" statement (With appropriate
escaping), but that just smells bad to me.

I'm using the amalgamation by default, so I don't think FTS is in the
DLL.  I can recompile, but that now introduces complications on
machines that may not have this exact DLL.  (A couple people in my
company use this app)

Is there any trick I can use that'll return AbcRfeDef and FedRfeAbc if
the two keywords are Abc and Def from a SQL call?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Jens Alfke-2


> On Nov 9, 2017, at 12:50 PM, Stephen Chrzanowski <[hidden email]> wrote:
>
> Is there any trick I can use that'll return AbcRfeDef and FedRfeAbc if
> the two keywords are Abc and Def from a SQL call?

Use full-text search (FTS4 or FTS5). This is exactly what it’s for.

Or as a hacky workaround, change the query to “… Title like ‘%Abc%’ and Title like ‘%Def%’”.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Peter da Silva
In reply to this post by Stephen Chrzanowski
On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <[hidden email] on behalf of [hidden email]> wrote:
> I've added a simple filter function that uses SQLites LIKE operator, but I'd like something a little bit more advanced.  Right now, the SQL code is like:
>
> select * from Events where Title like '%Abc%Def%'
>
>  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
>
> I've thought about doing some kind of delimiter, then have the code generate the SQL code by just looping through the keywords and generate the "or Title like '%keyword%'" statement (With appropriate escaping), but that just smells bad to me.

The best way to do it is not to use complex keys, but if you have to (say because the input is free form) then generating

SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;

is probably the only option.
 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Don V Nielsen
Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
about regular expression? (Abc|Def)

On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
[hidden email]> wrote:

> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
> [hidden email] on behalf of
> [hidden email]> wrote:
> > I've added a simple filter function that uses SQLites LIKE operator, but
> I'd like something a little bit more advanced.  Right now, the SQL code is
> like:
> >
> > select * from Events where Title like '%Abc%Def%'
> >
> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
> >
> > I've thought about doing some kind of delimiter, then have the code
> generate the SQL code by just looping through the keywords and generate the
> "or Title like '%keyword%'" statement (With appropriate escaping), but that
> just smells bad to me.
>
> The best way to do it is not to use complex keys, but if you have to (say
> because the input is free form) then generating
>
> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>
> is probably the only option.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Stephen Chrzanowski
I'd take regular expressions, sure, but, how do you get SQLite syntax
to take that on?

On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen <[hidden email]> wrote:

> Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
> about regular expression? (Abc|Def)
>
> On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
> [hidden email]> wrote:
>
>> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
>> [hidden email] on behalf of
>> [hidden email]> wrote:
>> > I've added a simple filter function that uses SQLites LIKE operator, but
>> I'd like something a little bit more advanced.  Right now, the SQL code is
>> like:
>> >
>> > select * from Events where Title like '%Abc%Def%'
>> >
>> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
>> >
>> > I've thought about doing some kind of delimiter, then have the code
>> generate the SQL code by just looping through the keywords and generate the
>> "or Title like '%keyword%'" statement (With appropriate escaping), but that
>> just smells bad to me.
>>
>> The best way to do it is not to use complex keys, but if you have to (say
>> because the input is free form) then generating
>>
>> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>>
>> is probably the only option.
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Don V Nielsen
Brent Ashley has a nice blog post on integrating your languages regular
express processing into sqlite:
http://www.ashleyit.com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/


On Fri, Nov 10, 2017 at 8:15 AM, Stephen Chrzanowski <[hidden email]>
wrote:

> I'd take regular expressions, sure, but, how do you get SQLite syntax
> to take that on?
>
> On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen <[hidden email]>
> wrote:
> > Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
> > about regular expression? (Abc|Def)
> >
> > On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
> > [hidden email]> wrote:
> >
> >> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
> >> [hidden email] on behalf of
> >> [hidden email]> wrote:
> >> > I've added a simple filter function that uses SQLites LIKE operator,
> but
> >> I'd like something a little bit more advanced.  Right now, the SQL code
> is
> >> like:
> >> >
> >> > select * from Events where Title like '%Abc%Def%'
> >> >
> >> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
> >> >
> >> > I've thought about doing some kind of delimiter, then have the code
> >> generate the SQL code by just looping through the keywords and generate
> the
> >> "or Title like '%keyword%'" statement (With appropriate escaping), but
> that
> >> just smells bad to me.
> >>
> >> The best way to do it is not to use complex keys, but if you have to
> (say
> >> because the input is free form) then generating
> >>
> >> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
> >>
> >> is probably the only option.
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Don V Nielsen
A good StackOverflow post on adapting in C# for use in Linq queries:
https://stackoverflow.com/questions/24229785/sqlite-net-sqlitefunction-not-working-in-linq-to-sql/26155359#26155359


On Fri, Nov 10, 2017 at 8:31 AM, Don V Nielsen <[hidden email]>
wrote:

> Brent Ashley has a nice blog post on integrating your languages regular
> express processing into sqlite: http://www.ashleyit.
> com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/
>
>
> On Fri, Nov 10, 2017 at 8:15 AM, Stephen Chrzanowski <[hidden email]>
> wrote:
>
>> I'd take regular expressions, sure, but, how do you get SQLite syntax
>> to take that on?
>>
>> On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen <[hidden email]>
>> wrote:
>> > Assuming you are looking for "Abc" or "Def" anywhere in the argument,
>> how
>> > about regular expression? (Abc|Def)
>> >
>> > On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
>> > [hidden email]> wrote:
>> >
>> >> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
>> >> [hidden email] on behalf of
>> >> [hidden email]> wrote:
>> >> > I've added a simple filter function that uses SQLites LIKE operator,
>> but
>> >> I'd like something a little bit more advanced.  Right now, the SQL
>> code is
>> >> like:
>> >> >
>> >> > select * from Events where Title like '%Abc%Def%'
>> >> >
>> >> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
>> >> >
>> >> > I've thought about doing some kind of delimiter, then have the code
>> >> generate the SQL code by just looping through the keywords and
>> generate the
>> >> "or Title like '%keyword%'" statement (With appropriate escaping), but
>> that
>> >> just smells bad to me.
>> >>
>> >> The best way to do it is not to use complex keys, but if you have to
>> (say
>> >> because the input is free form) then generating
>> >>
>> >> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>> >>
>> >> is probably the only option.
>> >>
>> >>
>> >> _______________________________________________
>> >> sqlite-users mailing list
>> >> [hidden email]
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> > _______________________________________________
>> > sqlite-users mailing list
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Richard Hipp-3
In reply to this post by Stephen Chrzanowski
On 11/10/17, Stephen Chrzanowski <[hidden email]> wrote:
> I'd take regular expressions, sure, but, how do you get SQLite syntax
> to take that on?

Load the regexp extension here
(https://www.sqlite.org/src/artifact/a68d25c659bd2d89) or one of the
other 3rd-party regexp extensions that you can find on the internet.
Then use the

     column REGEXP $pattern

operator.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Peter da Silva
Looking back through the thread, from the original post:

> I'm using the amalgamation by default, so I don't think FTS is in the DLL.  I can recompile, but that now introduces complications on machines that may not have this exact DLL.  (A couple people in my company use this app)

The same problem would presumably apply to the regex extension... not all users would have that extension.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Jens Alfke-2


> On Nov 10, 2017, at 6:50 AM, Peter Da Silva <[hidden email]> wrote:
>
>> I'm using the amalgamation by default, so I don't think FTS is in the DLL.  I can recompile, but that now introduces complications on machines that may not have this exact DLL.  (A couple people in my company use this app)
>
> The same problem would presumably apply to the regex extension... not all users would have that extension.

What platform is this for? Apple platforms ship with SQLite libraries that include FTS (not sure about regex, but it’d be easy to check.)

On other platforms, you can just statically link SQLite into your application and not have to worry what version or configuration might be on the user’s machine.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Stephen Chrzanowski
Thanks all for the info.  I'm still doing thought wrestling on the
path I want to take.

On one hand, its brain dead simple for me to go the tokenizer route
and have my application dynamically generate the SQL string, I just
don't like that kind of code style as there are many faults with it,
especially with how the wrapper deals with binding and user provided
data.

On the other hand, since at least one of my dev environments has the
tools already to do a Windows build of the amalgamation, I can write
out a batch file so that the DLL is built on a whim, with the required
extensions at the ready.  The magic touch is to find out how to get my
source code in the wrapper to handle the new kind of DLL naming (Since
I don't want a generic SQLite3.dll being in multiple places that do
different jobs) while the exact same wrapper code CAN handle
SQLite3.dll.  Much more work, but later on, might be useful for other
aspects.  The last rabbit out of the hat is to modify the installer to
put the new DLL in the application directory instead of
System32/Wherever, but that's easier to correct than doing the
tokenizer.

@Jens;
Although there are pay for, and free to use, libraries that can be
linked into my applications, I've never been satisfied with the code
provided.  Often times, it has so many dependencies on stuff that
isn't required that I'm just not going to put into my code.  Not to
mention, the routines that they provide are limited in some fashion
that just isn't compatible with my mindset.  The one library I did
find ( http://www.itwriting.com/blog/articles/a-simple-delphi-wrapper-for-sqlite-3
), and was easily able to modify for my own purposes, however,
requires external DLLs.

I've tried taking the amalgamation and convert it into something that
I can directly embed into my applications (Which would be great), but,
I've yet to be successful.  Obviously it can be done, but, I've not
been able to do it on my own.

On Fri, Nov 10, 2017 at 8:03 PM, Jens Alfke <[hidden email]> wrote:

>
>
>> On Nov 10, 2017, at 6:50 AM, Peter Da Silva <[hidden email]> wrote:
>>
>>> I'm using the amalgamation by default, so I don't think FTS is in the DLL.  I can recompile, but that now introduces complications on machines that may not have this exact DLL.  (A couple people in my company use this app)
>>
>> The same problem would presumably apply to the regex extension... not all users would have that extension.
>
> What platform is this for? Apple platforms ship with SQLite libraries that include FTS (not sure about regex, but it’d be easy to check.)
>
> On other platforms, you can just statically link SQLite into your application and not have to worry what version or configuration might be on the user’s machine.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Jens Alfke-2


> On Nov 11, 2017, at 3:41 PM, Stephen Chrzanowski <[hidden email]> wrote:
>
> Although there are pay for, and free to use, libraries that can be
> linked into my applications, I've never been satisfied with the code
> provided.

Hm, I didn’t say anything about 3rd party libraries, so I’m not sure what this is a response to.

> I've tried taking the amalgamation and convert it into something that
> I can directly embed into my applications (Which would be great), but,
> I've yet to be successful.

Just add sqlite3.c as a source file in your project/makefile/whatever.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Stephen Chrzanowski
I'm not using a C compiler.  Delphi Berlin doesn't compile C, AFAIK.
I'll play around with that tonight as I've got a project I need to
start up tonight.

Third party libraries include those like LiteDAC, or DISQLite3, or
Synopse mORMot framework.


On Mon, Nov 13, 2017 at 1:34 PM, Jens Alfke <[hidden email]> wrote:

>
>
>> On Nov 11, 2017, at 3:41 PM, Stephen Chrzanowski <[hidden email]> wrote:
>>
>> Although there are pay for, and free to use, libraries that can be
>> linked into my applications, I've never been satisfied with the code
>> provided.
>
> Hm, I didn’t say anything about 3rd party libraries, so I’m not sure what this is a response to.
>
>> I've tried taking the amalgamation and convert it into something that
>> I can directly embed into my applications (Which would be great), but,
>> I've yet to be successful.
>
> Just add sqlite3.c as a source file in your project/makefile/whatever.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Warren Young
On Nov 13, 2017, at 3:26 PM, Stephen Chrzanowski <[hidden email]> wrote:
>
> I'm not using a C compiler.

This article appears to sort that out:

    http://rvelthuis.de/articles/articles-cobjs.html

Basically, you download the free CLI-only version of the C++Builder Berlin compiler, build sqlite3.c to sqlite3.obj — possibly along with other C code of your own, plus extensions — and then link that into your Delphi project using the instructions provided.

Then no worries about random DLLs floating around.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Search using LIKE or something else

Stephen Chrzanowski
Thanks Warren.  I'll bookmark this and have a read over it maybe this
weekend.  (Busy work week, and I'd just rather chill with Factorio,
The Crew, or some other thing that lowers the stress. :] )

On Tue, Nov 14, 2017 at 10:58 AM, Warren Young <[hidden email]> wrote:

> On Nov 13, 2017, at 3:26 PM, Stephen Chrzanowski <[hidden email]> wrote:
>>
>> I'm not using a C compiler.
>
> This article appears to sort that out:
>
>     http://rvelthuis.de/articles/articles-cobjs.html
>
> Basically, you download the free CLI-only version of the C++Builder Berlin compiler, build sqlite3.c to sqlite3.obj — possibly along with other C code of your own, plus extensions — and then link that into your Delphi project using the instructions provided.
>
> Then no worries about random DLLs floating around.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users