SQL 6.5 'LIKE' question

[Jeff Baker]

Is it possible to use a column pulled from a table in the LIKE clause of a SELECT statement?

For example, I need to determine if any words saved in one table exist in the text of a text field from a different table using the LIKE clause. The match string clause does not appear to allow this.

I've tried something like the following but it doesn't appear to work:

SELECT emails.mail_id, emails.body_text FROM emails, words
WHERE emails.body_text LIKE '%words.word%'

I'm assuming the SELECT statement will look for the literal "words.word" in the body_text column instead of the value of that column.

Any suggestions?
TIA,
Jeff

[Simon McAlister]

Jeff,
What you are attempting requires that you use the execute statement.

If you wish to use a column name that is passed as a variable, you must build up the SQL statement you wish to run into a string and then execute a string.

Eg
declare @sql varchar(255)
select @sql = "SELECT emails.mail_id, emails.body_text FROM emails, words
WHERE emails.body_text LIKE " +@word
execute (@sql)

You are still left with the problem of calculating the value of @word.
It should be possible with some use of the system table syscolumns.





------------
Jeff Baker at 6/18/99 11:44:34 AM

Is it possible to use a column pulled from a table in the LIKE clause of a SELECT statement?

For example, I need to determine if any words saved in one table exist in the text of a text field from a different table using the LIKE clause. The match string clause does not appear to allow this.

I've tried something like the following but it doesn't appear to work:

SELECT emails.mail_id, emails.body_text FROM emails, words
WHERE emails.body_text LIKE '%words.word%'

I'm assuming the SELECT statement will look for the literal "words.word" in the body_text column instead of the value of that column.

Any suggestions?
TIA,
Jeff