Re: [sqlite] looking up records with terms in a table

[Gert Van Assche]

Nice

2013/3/26 Petite Abeille :
>
> On Mar 26, 2013, at 10:36 PM, "Paul Mathieu"  wrote:
>
>> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE
>> CONCAT('%',T2.Terms,'%')
>
> Alternatively, use FTS [1]:
>
> sqlite> create virtual table  sentence using fts4( content text );
> sqlite> insert into sentence values( 'FTS3 and FTS4 are nearly identical' );
> sqlite> create table term( content text );
> sqlite> insert into term values( 'nearly' );
> sqlite> select * from sentence join term on sentence.content match 
> term.content;
> FTS3 and FTS4 are nearly identical|nearly
>
> [1] http://www.sqlite.org/fts3.html
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

[Petite Abeille]

On Mar 26, 2013, at 10:36 PM, "Paul Mathieu"  wrote:

> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE
> CONCAT('%',T2.Terms,'%')

Alternatively, use FTS [1]:

sqlite> create virtual table  sentence using fts4( content text );
sqlite> insert into sentence values( 'FTS3 and FTS4 are nearly identical' );
sqlite> create table term( content text );
sqlite> insert into term values( 'nearly' );
sqlite> select * from sentence join term on sentence.content match 
term.content; 
FTS3 and FTS4 are nearly identical|nearly

[1] http://www.sqlite.org/fts3.html

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

Re: [sqlite] looking up records with terms in a table

[Paul Mathieu]

Try this:
SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE
CONCAT('%',T2.Terms,'%')

Paul

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson
Sent: Tuesday, March 26, 2013 10:27 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] looking up records with terms in a table

I think you need wildcards:

SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE %T2.Terms%

RobR, not guaranteeing correct syntax

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche
Sent: Tuesday, March 26, 2013 12:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] looking up records with terms in a table

Clemens, doesn't seem to work...

The terms are just a part of the sentence, not a full match.
Your query does find full matches.

thanks

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


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

Re: [sqlite] looking up records with terms in a table

[Gert Van Assche]

Bingo!
Thanks you all!

2013/3/26 Clemens Ladisch :
> Gert Van Assche wrote:
>> 2013/3/26 Clemens Ladisch :
>>> Gert Van Assche wrote:
 What I would like to do is look for all terms that appear in the Terms 
 table.
 Something like this (but of course this does not work):
 SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);
>>>
>>> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE T2.Terms
>>
>> Clemens, doesn't seem to work...
>>
>> The terms are just a part of the sentence, not a full match.
>> Your query does find full matches.
>
> That's how you wrote it in your original query...
>
> Just add wildcards around the terms:
>
> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE '%' || T2.Terms || '%'
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

[Clemens Ladisch]

Gert Van Assche wrote:
> 2013/3/26 Clemens Ladisch :
>> Gert Van Assche wrote:
>>> What I would like to do is look for all terms that appear in the Terms 
>>> table.
>>> Something like this (but of course this does not work):
>>> SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);
>>
>> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE T2.Terms
>
> Clemens, doesn't seem to work...
>
> The terms are just a part of the sentence, not a full match.
> Your query does find full matches.

That's how you wrote it in your original query...

Just add wildcards around the terms:

SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE '%' || T2.Terms || '%'


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

Re: [sqlite] looking up records with terms in a table

[James K. Lowden]

On Tue, 26 Mar 2013 17:14:57 +0100
Gert Van Assche  wrote:

> SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE '%GM%';
> 
> What I would like to do is look for all terms that appear in the
> Terms table. Something like this (but of course this does not work):
> SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms
> FROM T2);

SELECT Sentences FROM T1 
WHERE EXISTS (
select 1 from T2
where T1.Sentences like '%' || Terms || '%'
);

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

Re: [sqlite] looking up records with terms in a table

[Gert Van Assche]

Rob, no that does not work either.
Thanks for trying though.

2013/3/26 Rob Richardson <rdrichard...@rad-con.com>:
> I think you need wildcards:
>
> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE %T2.Terms%
>
> RobR, not guaranteeing correct syntax
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche
> Sent: Tuesday, March 26, 2013 12:53 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] looking up records with terms in a table
>
> Clemens, doesn't seem to work...
>
> The terms are just a part of the sentence, not a full match.
> Your query does find full matches.
>
> thanks
>
> gert
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

[Rob Richardson]

I think you need wildcards:

SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE %T2.Terms%

RobR, not guaranteeing correct syntax

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Gert Van Assche
Sent: Tuesday, March 26, 2013 12:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] looking up records with terms in a table

Clemens, doesn't seem to work...

The terms are just a part of the sentence, not a full match.
Your query does find full matches.

thanks

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

Re: [sqlite] looking up records with terms in a table

[Gert Van Assche]

Clemens, doesn't seem to work...

The terms are just a part of the sentence, not a full match.
Your query does find full matches.

thanks

gert


2013/3/26 Clemens Ladisch :
> Gert Van Assche wrote:
>> What I would like to do is look for all terms that appear in the Terms table.
>> Something like this (but of course this does not work):
>> SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);
>
> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE T2.Terms
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

[Clemens Ladisch]

Gert Van Assche wrote:
> What I would like to do is look for all terms that appear in the Terms table.
> Something like this (but of course this does not work):
> SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);

SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE T2.Terms


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