Re: [sqlite] Does SQLite have a fulltext search like MySQL?

[Jim Dodgen]

look at the glob command it works like like except uses the unix file name 
globbing style of matching

not quite a grep

select * from table where a glob '[abc]foobar*';

At 06:47 PM 7/5/2005, you wrote:

like this?
select * from table where a like '%abc%';


SQLite also makes it easy to write your own functions.  That way you
can define other matching algorithms (eg Jaro-Winkler).

Roger

Re: [sqlite] Does SQLite have a fulltext search like MySQL?

[Roger Binns]

like this?
select * from table where a like '%abc%';


SQLite also makes it easy to write your own functions.  That way you
can define other matching algorithms (eg Jaro-Winkler).

Roger

Re: [sqlite] Does SQLite have a fulltext search like MySQL?

[Kurt Welgehausen]

> From: Stephen Leaf <[EMAIL PROTECTED]>
> Organization: SMiLeaf
> To: sqlite-users@sqlite.org
> Date: Tue, 5 Jul 2005 18:06:39 -0500
> Subject: Re: [sqlite] Does SQLite have a fulltext search like MySQL?
>
> On Tuesday 05 July 2005 04:48 pm, Michael Grice wrote:
> > If not, are there plans to add this?
> >
> > I'm just a NOOB, looking to see what all SQLite can do for me, before
> > I go too far down this road.
> >
> > Thx.
>
> like this?
> select * from table where a like '%abc%';
>
SQLite does not support full-text searches. This has been
discussed on the list before. I don't know of any plans
to add it, but maybe someone more familiar with develop-
ment plans can give you a better answer; or you could
search the archives:

  


Regards

Re: [sqlite] Does SQLite have a fulltext search like MySQL?

[Stephen Leaf]

On Tuesday 05 July 2005 04:48 pm, Michael Grice wrote:
> If not, are there plans to add this?
>
> I'm just a NOOB, looking to see what all SQLite can do for me, before
> I go too far down this road.
>
> Thx.

like this?
select * from table where a like '%abc%';

Re: [sqlite] Search question

[Stephen Leaf]

On Tuesday 05 July 2005 05:31 pm, Tom Shaw wrote:
> Is there an easy way to perform a search on VARCHAR/CHAR/TEXT column
> for those entries that has strings that are longer than x without
> writing my own comparitor? I am using SQLite in a PHP environment, I
> am looking for an intrinsic function that would run natively rather
> than a interpretive PHP function to preform this comparison.

checkout the length('string') function.

select * from table where length(a) > length(b);

>
> All help is appreciated,
>
> Tom

[sqlite] Search question

[Tom Shaw]

Is there an easy way to perform a search on VARCHAR/CHAR/TEXT column 
for those entries that has strings that are longer than x without 
writing my own comparitor? I am using SQLite in a PHP environment, I 
am looking for an intrinsic function that would run natively rather 
than a interpretive PHP function to preform this comparison.


All help is appreciated,

Tom

[sqlite] Does SQLite have a fulltext search like MySQL?

[Michael Grice]

If not, are there plans to add this?

I'm just a NOOB, looking to see what all SQLite can do for me, before
I go too far down this road.

Thx.

Re: [sqlite] how to do select by Date and Time range?

[Stephen Leaf]

On Tuesday 05 July 2005 01:23 pm, jack wu wrote:
> one more question if i may. what should i do if i 'd
> like to have two columns, one for Date, one for Time
> of the day. most of the times, i 'd like to query by
> Date only but i want to display date and time at the
> same time. is julianday('2005-07-01') going to store
> any time information? Thanks.

yes it does.
sqlite> select datetime(julianday('2005-07-01'));
2005-07-01 00:00:00

time is midnight ;)

>
> jack.
>
> --- Stephen Leaf <[EMAIL PROTECTED]> wrote:
> > On Tuesday 05 July 2005 09:53 am, Cory Nelson wrote:
> > > Just an educated guess, but probably because
> >
> > sqlite tries to be as
> >
> > > minimal as possible.  Which I have no complaints
> >
> > with, as comparing a
> >
> > > double will likely be faster than comparing a
> >
> > string.
> > I personally store all mine like this anyway using
> > unix time so I can change
> > the format at anytime. plus it's not only faster to
> > compare programming wise,
> > just compare numbers. no need use functions.
> > strings also are larger than numbers in size wise so
> > you save a few bytes here
> > and there.
> >
> > > On 7/5/05, Johan Danielsson <[EMAIL PROTECTED]>
> >
> > wrote:
> > > > Cory Nelson <[EMAIL PROTECTED]> writes:
> > > > > create table t_foo(bar real);
> > > > > insert into t_foo
> >
> > values(julianday('2005-07-01'));
> >
> > > > Is there any advantage to this compared to
> >
> > storing dates as strings in
> >
> > > > (for instance) ISO8601 format?
> > > >
> > > > /Johan

Re: [sqlite] how to do select by Date and Time range?

[jack wu]

one more question if i may. what should i do if i 'd
like to have two columns, one for Date, one for Time
of the day. most of the times, i 'd like to query by
Date only but i want to display date and time at the
same time. is julianday('2005-07-01') going to store
any time information? Thanks.

jack.


--- Stephen Leaf <[EMAIL PROTECTED]> wrote:

> On Tuesday 05 July 2005 09:53 am, Cory Nelson wrote:
> > Just an educated guess, but probably because
> sqlite tries to be as
> > minimal as possible.  Which I have no complaints
> with, as comparing a
> > double will likely be faster than comparing a
> string.
> I personally store all mine like this anyway using
> unix time so I can change 
> the format at anytime. plus it's not only faster to
> compare programming wise, 
> just compare numbers. no need use functions.
> strings also are larger than numbers in size wise so
> you save a few bytes here 
> and there.
> >
> > On 7/5/05, Johan Danielsson <[EMAIL PROTECTED]>
> wrote:
> > > Cory Nelson <[EMAIL PROTECTED]> writes:
> > > > create table t_foo(bar real);
> > > > insert into t_foo
> values(julianday('2005-07-01'));
> > >
> > > Is there any advantage to this compared to
> storing dates as strings in
> > > (for instance) ISO8601 format?
> > >
> > > /Johan
> 

Re: [sqlite] how to use the LIMIT and OFFSET?

[Eric Scouten]

jack wu wrote:


i am trying to run some web search like query. the one
that returns a total number of results and only
presents the first 20, then allows user to click on
next to go to the next 20 etc.

has anyone done this before? i suppose i would use
LIMIT or OFFSET in the select statement, but the
offset only skips certain number of results before the
results i want to show, am i right?

so if i have 60 results, using OFFSET, i could skip
the first 20, and get the rest of 40, but i can't say,
give me the second 20.


First 20: SELECT ... LIMIT 20 OFFSET 0;

Next 20: SELECT ... LIMIT 20 OFFSET 20;

Next 20 after that: SELECT ... LIMIT 20 OFFSET 40;

etc.


"OFFSET n" basically means skip the first "n" rows that would have been 
returned otherwise.


"LIMIT n" says stop returning rows after the "n"th row, even if more 
rows would otherwise be available.


-Eric

Re: [sqlite] how to do select by Date and Time range?

[Stephen Leaf]

On Tuesday 05 July 2005 09:53 am, Cory Nelson wrote:
> Just an educated guess, but probably because sqlite tries to be as
> minimal as possible.  Which I have no complaints with, as comparing a
> double will likely be faster than comparing a string.
I personally store all mine like this anyway using unix time so I can change 
the format at anytime. plus it's not only faster to compare programming wise, 
just compare numbers. no need use functions.
strings also are larger than numbers in size wise so you save a few bytes here 
and there.
>
> On 7/5/05, Johan Danielsson <[EMAIL PROTECTED]> wrote:
> > Cory Nelson <[EMAIL PROTECTED]> writes:
> > > create table t_foo(bar real);
> > > insert into t_foo values(julianday('2005-07-01'));
> >
> > Is there any advantage to this compared to storing dates as strings in
> > (for instance) ISO8601 format?
> >
> > /Johan

Re: [sqlite] how to do select by Date and Time range?

[Cory Nelson]

Just an educated guess, but probably because sqlite tries to be as
minimal as possible.  Which I have no complaints with, as comparing a
double will likely be faster than comparing a string.

On 7/5/05, Johan Danielsson <[EMAIL PROTECTED]> wrote:
> Cory Nelson <[EMAIL PROTECTED]> writes:
> 
> > create table t_foo(bar real);
> > insert into t_foo values(julianday('2005-07-01'));
> 
> Is there any advantage to this compared to storing dates as strings in
> (for instance) ISO8601 format?
> 
> /Johan
> 


-- 
Cory Nelson
http://www.int64.org

[sqlite] Syntax Help

[Ray Mosley]

AS a DB rookie, I have replaced the .txt files in an Tcl/Tk application with 
a SQLite database, so it still reads very much like file I/O. While in a 
loop I wrote several records to my files, so now I simply do an INSERT.
 I keep reading that you optimize performance by using transactions, so how 
do I do that?
 Do I simply create a string that is my SQL statements, and how do I commit 
the changes - never having done that in the past? My code kind of looks 
like:
 
set tran_string "BEGIN TRANSACTION\n"
 foreach ...
 append tran_string "\n"
append tran_string "END TRANSACTION\nCOMMIT TRANSACTION"
DB eval "$tran_string"

 Thanks for the help.
-- 
Ray Mosley

Re: [sqlite] how to do select by Date and Time range?

[Johan Danielsson]

Cory Nelson <[EMAIL PROTECTED]> writes:

> create table t_foo(bar real);
> insert into t_foo values(julianday('2005-07-01'));

Is there any advantage to this compared to storing dates as strings in
(for instance) ISO8601 format?

/Johan

Re: [sqlite] how to do select by Date and Time range?

[Cory Nelson]

create table t_foo(bar real);
insert into t_foo values(julianday('2005-07-01'));

On 7/4/05, jack wu <[EMAIL PROTECTED]> wrote:
> Cory, thanks for the information. the wiki talks about
> time functions. what should we do when defining the
> table columns? should i just use REAL? can you give a
> sample CREATE table statement? and a sample insert
> statement?
> 
> 
> jack.
> 
> 
> 
> 


-- 
Cory Nelson
http://www.int64.org

Re: [sqlite] What happens to unused space?

[Dirk Theisen]

Hi!


If I do this*, is anyone else interested in it?  Obviously it would
be a compile time option, as it would probably impact performance
(but not to a level that matters for my application).


I think a persistent pragma would be more appropriate, don't you think?

Greetings,
  Dirk

Re: [sqlite] Another SQLite documentation/manual

[Firman Wandayandi]

On 7/5/05, Roger Binns <[EMAIL PROTECTED]> wrote:
> > Anything about sqlite_master table information I guess, or maybe I missed 
> > it?
> 
> http://www.sqlite.org/faq.html#q9
> 

Ok, I missed the faqs. Thanks.

> I prefer using the pragmas:
> 
> http://www.sqlite.org/pragma.html#schema
> 
> Roger
> 
-- 
:: Never Dreamt Before ::
http://php.hm/~firman/
firman(@)php.net | firmanw(@)gmail.com

Re: [sqlite] Another SQLite documentation/manual

[Roger Binns]

Anything about sqlite_master table information I guess, or maybe I missed it?


http://www.sqlite.org/faq.html#q9

I prefer using the pragmas:

http://www.sqlite.org/pragma.html#schema

Roger