On Sat, Jun 25, 2011 at 04:55:13PM -0400, Igor Tandetnik scratched on the wall:
> logan...@gmail.com wrote:
> > How do I check for a null or empty string in SQLite. SQL server has ISNULL
> > but it doesn't seem to be supported in SQLite.
>
> where MyField is null
where MyField isnull is als
If you can't have two : after each other then this should work:
SELECT LTRIM(LTRIM('abc:xyz',REPLACE('abc:xyz',':','')),':')
SELECT LTRIM(LTRIM(Field1,REPLACE(Field1,':','')),':') FROM Table1
--
View this message in context:
http://old.nabble.com/Substring-question-tp31924687p31928255.html
Sent
logan...@gmail.com wrote:
> How do I check for a null or empty string in SQLite. SQL server has ISNULL
> but it doesn't seem to be supported in SQLite.
where MyField is null
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http:
On Fri, Jun 24, 2011 at 11:36 PM, Roger Binns wrote:
> On 06/24/2011 08:28 AM, Cheetah wrote:
>> However, AFAICT, the standard form of a file:
>> URI on windows for a UNC path uses exactly that.
>
> Are you proposing that SQLite somehow work out if the supplied name maps
> back to the local machin
On Sat, Jun 25, 2011 at 7:25 AM, Luuk wrote:
> I create a test database with almost 10 milion rows.
> I'm surprised to see that the first SELECT below is much slower than the
> sum of the next three SELECTs.
>
> Can anyone give a hint why this is the case?
>
> SQLite version 3.7.2
> Enter ".help"
On 25 Jun 2011, at 7:14pm, Black, Michael (IS) wrote:
> I guess my point is why doesn't the optimizer recognize the almost-zero cost
> of min/max in the compound query? It assumes instead to do it all in one scan.
Because it's almost impossible to optimize sub-SELECTs. You pretty-much have
to
On Jun 25, 2011, at 3:33 PM, logan...@gmail.com wrote:
> Hello,
>
> How do I check for a null or empty string in SQLite. SQL server has ISNULL
> but it doesn't seem to be supported in SQLite.
ifnull() and nullif() [http://www.sqlite.org/lang_corefunc.html]
__
Hello,
How do I check for a null or empty string in SQLite. SQL server has ISNULL
but it doesn't seem to be supported in SQLite.
Thanks,
Hitesh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-
On 25-06-2011 20:14, Black, Michael (IS) wrote:
> So these two queries should be equal in speed and plan:
>
>
>
>
>
> select * from (select min(i) from test) as a,(select max(i) from
> test) as b, (select count(i) from test) as c; select
> min(i),max(i),count(i) from test;
and, to make the
I guess my point is why doesn't the optimizer recognize the almost-zero cost of
min/max in the compound query? It assumes instead to do it all in one scan.
So these two queries should be equal in speed and plan:
select * from (select min(i) from test) as a,(select max(i) from test) as b,
On Sat, 25 Jun 2011 19:42:11 +0200, Cecil Westerhof
wrote:
>Thanks for the answers. I had thought about both options, but was wondering
>if I had missed something better. I original opted for the LIMIT/OFFSET
>option (because the order is important), but after following the link I
>found that thi
2011/6/25 Cecil Westerhof
> With LIMIT you can get the first N records of a SELECT. Is it also possible
> to get the last N records?
>
Thanks for the answers. I had thought about both options, but was wondering
if I had missed something better. I original opted for the LIMIT/OFFSET
option (becau
On 25 Jun 2011, at 4:12pm, Black, Michael (IS) wrote:
> Can the optimizer recognize these "instant" functions and perhaps lean
> towards using them?
It already does. That's why max(x) takes no time when you have an index on (x).
> Are there cases where this would NOT be good?
Theoretically,
Can the optimizer recognize these "instant" functions and perhaps lean towards
using them?
Are there cases where this would NOT be good?
I did show you can construct a query which runs at the "correct" speed.
sqlite> select * from (select min(i) from test) as a,(select max(i) from test)
as
On 25 Jun 2011, at 1:37pm, Luuk wrote:
> Giving the SELECTs i a different order does not improve thing (much).
> BTW, i'm doing this on a Windows 7 machine, and will test what happens
> on a Linux machine too, to see if there's difference.
>
> SQLite version 3.7.2
> Enter ".help" for instruction
On 25-06-2011 15:15, Black, Michael (IS) wrote:
> OK...I generated 10M ints and imported them. I get times that I would expect.
>
> select with all 3 is slightly less then the sum of the other 3 individually.
>
>
>
> main()
> {
> int i;
> for(i=0;i<1000;i++) {
>
On 25-06-2011 15:37, Black, Michael (IS) wrote:
> And...this select does it at the speed you expect since it uses the indexes
> for min/max.\
>
>
>
> This one I ran with 3.7.7
>
>
>
> sqlite> select * from (select min(i) from test) as a,(select max(i) from
> test) as b, (select count(i) fro
And...this select does it at the speed you expect since it uses the indexes for
min/max.\
This one I ran with 3.7.7
sqlite> select * from (select min(i) from test) as a,(select max(i) from test)
as b, (select count(i) from test) as c;
min( max(i) coun
-
37
OK...I generated 10M ints and imported them. I get times that I would expect.
select with all 3 is slightly less then the sum of the other 3 individually.
main()
{
int i;
for(i=0;i<1000;i++) {
int j=rand();
printf("%d\n",j);
}
}
sq
sorry, my reaction is at the bottom ;)
On 25-06-2011 13:59, Black, Michael (IS) wrote:
> Ucaching?
>
>
>
> Try doing your first select last and see what happens. I'm betting your
> first "select min(*) from test" will be a lot slower.
>
>
>
> Michael D. Black
>
> Senior Scientist
Ucaching?
Try doing your first select last and see what happens. I'm betting your first
"select min(*) from test" will be a lot slower.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-b
I create a test database with almost 10 milion rows.
I'm surprised to see that the first SELECT below is much slower than the
sum of the next three SELECTs.
Can anyone give a hint why this is the case?
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
Hi Richard,
Do you have a plan to implement "common subexpression elimination" ?
In the attached database, when I try to execute "select * from
v_category_display", performances are very bad and I don't know how to correct
this problem.
"common subexpression elimination" is the best solution f
I need to select a substring of a column that starts 1 character after a
colon in the column and continues to the end of the column. For example, if
the column contained "abc:xyz" I want the select statement to return only
"xyz" (the characters after the colon). The substr function requires
speci
On Sat, Jun 25, 2011 at 9:53 AM, Pete wrote:
> I need to select a substring of a column that starts 1 character after a
> colon in the column and continues to the end of the column. For example,
> if
> the column contained "abc:xyz" I want the select statement to return only
> "xyz" (the charact
On Sat, 25 Jun 2011 09:26:42 +0200, Cecil Westerhof
wrote:
> With LIMIT you can get the first N records of a SELECT.
> Is it also possible to get the last N records?
Yes, I can think of two ways:
1- Reverse the ORDER BY (make it DESC), so you can use the exact
same LIMIT clause,
2- replace th
On 06/25/11 09:26 AM, Cecil Westerhof wrote:
> With LIMIT you can get the first N records of a SELECT. Is it also possible
> to get the last N records?
Search for LIMIT/OFFSET on http://www.sqlite.org/lang_select.html
/Roger
___
sqlite-users mailing lis
On Sat, Jun 25, 2011 at 11:26 AM, Cecil Westerhof wrote:
> With LIMIT you can get the first N records of a SELECT. Is it also possible
> to get the last N records?
>
>
Use ORDER BY ... DESC. Sure, if you have a large dataset, it'd better be
indexed.
Max
___
With LIMIT you can get the first N records of a SELECT. Is it also possible
to get the last N records?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
29 matches
Mail list logo
