Re: [sqlite] Substring question

[marbex]

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 from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL: SQLite query help pls

[marbex]

Black, Michael (IS) wrote:
> 
> Since there aren't a whole lot of string manipulaion functions (like
> indexof or such) try this:
>  
> sqlite> create table t(s varchar);
> sqlite> create table t2(s varchar);
> sqlite> insert into t values('C:\richEminem\file.txt');
> sqlite> select rtrim(s,'._
> abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from
> t;
> C:\richEminem\folder.jpg
> sqlite> insert into t2(s)  select rtrim(s,'._
> abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from
> t;
> sqlite> select * from t2;
> C:\richEminem\folder.jpg
>  
> You do, of course, need to put all allowable characters in the rtrim
> character set (except the backslash or forward slash).
>  
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>  
> 
I came up with this SQL some time ago when I needed to get the folder from a
full path, the beauty with it is that it always works regardless of which
characters you have in the filename. I though I'd share it.

select RTRIM(path,REPLACE(path,'\','')) from 
(select 'C:\richEminem\file.txt' path)

-- 
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p29985874.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 phantom token?...

[marbex]

Shopsland gmail wrote:
> 
>select title from fts_news where fts_news match 'ined'
> 

It looks that you only want to query the title field, so the query should
be:
select title from fts_news where title match 'ined'
or
select title from fts_news where fts_news match 'title:ined'
-- 
View this message in context: 
http://www.nabble.com/FTS3-phantom-token-...-tp25594632p25629682.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any way to speed up this SQL?

[marbex]

I had a similar issue. I wanted to find strings that started as another
string in the same table and field. My solution was to create a temp table
that consisted of the id, the string and the first word of the string which
I then indexed. The table had 30 000 records and the total processing time
went down from 15-20 minutes to 15 seconds!

Applying that solution to your case, not knowing the nature of your data (I
had names), I guess you can do something like this:

- Get the length of the shortest string in table2.term. Lets say it's 3.

- Create a tmptable of table1
Create temp tmptable1 as
select term, lower(substr(term,1,3)) shortest
from table1

- Create a tmptable of table2
Create temp tmptable2 as
select term, lower(substr(term,1,3)) shortest
from table2

- Index the shortest fields
Create index idx_tmptable1_shortest on tmptable1(shortest)
Create index idx_tmptable2_shortest on tmptable2(shortest)

-Run this sql
select
count(a.rowid)
from tmptable1 a inner join tmptable2 b on a.shortest=b.shortest
where (lower(b.term) = lower(substr(a.term,1,length(b.term



RB Smissaert wrote:
> 
> Have 2 tables with both one text field called term and need to run a SQL
> like this, to count the records in table1 where the start of term in
> table1
> equals a term in table2:
> 
> select
> count(a.rowid)
> from table1 a inner join table2 b on
> (lower(b.term) = lower(substr(a.term,1,length(b.term
> 
> term is indexed in both tables, but not surprisingly, this query runs very
> slow, taking some 40 minutes. Table1 is large, maybe some 1 million rows
> and
> table2 is small, maybe some 30.000 rows. All rows in table2 are unique,
> but
> table1 has many duplicates.
> 
> Any suggestions to speed this up?
> I could also tackle this in code rather than in SQL.
> 
> RBS
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Any-way-to-speed-up-this-SQL--tp25412299p25413614.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users