468,720 Members | 1,560 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,720 developers. It's quick & easy.

SQL Query: where LIKE pattern is from another table

Hi,

I am trying to find rows where a memo field in table1 contains the text in
table2.

Example:

table1.memo contains:
row 1: This is a long sentence that goes on and on...
row 2: This is a another long text string that goes on and on...
etc.

table2.text contains:
row 1: sentence
row 2: forever
etc.

The query should return row 1 from table1 because %sentence% exists in memo. But
it would return nothing for the second entry %forever%.

Basically the query should look like this:
SELECT table1.memo
FROM table1
WHERE table1.memo LIKE '%table2.text%'

but that obviously does not work. So how do I loop through each record in table1
and find all the rows containing patterns from any of the rows in table2?

I tried using WHERE table1.memo LIKE IN (SELECT ... but it does not seem to work
either.

Any ideas?
Nov 13 '05 #1
5 14816
"Jack" <wh*@where.com> wrote in message
news:41***************@news-server.welho.com...
I am trying to find rows where a memo field in table1 contains the text in
table2.


You can't search inside memo fields.

Bruno
Nov 13 '05 #2
"Bruno Campanini" <br****************@tin.it> wrote in message
news:3I**********************@news4.tin.it...
"Jack" <wh*@where.com> wrote in message
news:41***************@news-server.welho.com...
I am trying to find rows where a memo field in table1 contains the text in table2.


You can't search inside memo fields.


Huh? You can't sort on them or index them, but you can certainly search on
them. It's just not particularly efficient.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #3
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:2v*************@uni-berlin.de...
Huh? You can't sort on them or index them, but you can certainly search
on
them. It's just not particularly efficient.


You are right.
Sorry, my mistake.

Bruno
Nov 13 '05 #4
Jack <wh*@where.com> wrote in
news:41***************@news-server.welho.com:
Hi,

I am trying to find rows where a memo field in table1 contains
the text in table2.

Example:

table1.memo contains:
row 1: This is a long sentence that goes on and on...
row 2: This is a another long text string that goes on and
on... etc.

table2.text contains:
row 1: sentence
row 2: forever
etc.

The query should return row 1 from table1 because %sentence%
exists in memo. But it would return nothing for the second
entry %forever%.

Basically the query should look like this:
SELECT table1.memo
FROM table1
WHERE table1.memo LIKE '%table2.text%'

but that obviously does not work. So how do I loop through
each record in table1 and find all the rows containing
patterns from any of the rows in table2?

I tried using WHERE table1.memo LIKE IN (SELECT ... but it
does not seem to work either.

Any ideas?


SELECT table1.memo
FROM table1, table2
WHERE table1.memo LIKE '%' & table2.text & '%'
--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #5
On Wed, 10 Nov 2004 23:28:52 GMT, Bob Quintal <rq******@sPAmpatico.ca> wrote:

SELECT table1.memo
FROM table1, table2
WHERE table1.memo LIKE '%' & table2.text & '%'


D'oh, I forgot about the & operator to concatenate strings. I use it constantly
in VBA but I didn't realize it would work here as well.

By the way, SQL in MS-Access actually uses * instead of %. At least the above
query only works as:

SELECT table1.memo
FROM table1, table2
WHERE table1.memo LIKE '*' & table2.text & '*'

Just in case someone else reads this post.

Another question. Does anyone know of a way to optimize the above query idea. I
am processing a database with upwards of 50,000 rows (in seperate tables) of
longer text entries which need to be compared to another database of 30,000 and
some words. The idea is to find the rows where the two meet or match.

Are there any specific applications built for this? What are some optimizations
that could be done?
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Daniel Wetzler | last post: by
9 posts views Thread by =?Utf-8?B?RnJhbmsgVXJheQ==?= | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Oskars | last post: by
9 posts views Thread by bryonone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.