Finding words in a phrase...

[Tankgirl]

I have a large table where one of the columns is a book title (ex. "HOUSE OF SAND AND FOG&quot. I want users to be able to search on that column with one or more words from a form entry ("HOUSE" and "FOG&quot. Is this possible, or must I form a second table with individual phrase words? If possible, how? I cannot find this in any forum or in any of the 8 books I own on ASP and SQL!

Thanks in advance,

Kellen

 

[tlbroadbent]

Select * From table
Where title Like "*house*"
And title Like "*fog*"

NOTE: Using the LIKE predicate is not very efficient. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tlbroadbent]

If you are using SQL Server, you should use single quotes rather than double quotes.

I recommend posting questions in a forum for you RDMS rather than the ANSI SQL forum as each RDMS has different levels of compliance with the ANSI standard and most have extensions that can be useful.

For example, in T-SQL for SQL Server you could also use the CharIndex function as in the following query.

Select * From Table
Where CharIndex('house', Title)>0
And CharIndex('fog', Title)>0 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[carp]

And in Oracle it would be:

Select * From table
Where title Like '%house%'
And title Like '%fog%';

However, you will probably also want to make this case-insensitive, so you would want to do something like this:

Select * From table
Where upper(title) Like '%HOUSE%'
And upper(title) Like '%FOG%';