Keyword Search Implementation

Hi,

We are trying to implement keyword search of some tables in our database.

Unfortunately, the required SQL statement is getting quite involved. For example, if we are looking for the keyword 'blue', the following sql statement:

select id1 into var_id1
from my_table
where my_search_field like '%blue%';

will return rows that contain the substring 'blue' instead of the word 'blue'. For the word 'blue', the where clause would have to be something like:

where my_search_field like '% blue %'

or my_search_field like '% blue.%'
or my_search_field like '%.blue %'
or my_search_field like '% blue'
or my_search_field like 'blue.%'

It gets involved. Of course, another approach is to normalize my_search_field in another column just for searching purposes. But I'd like to know what others out there are doing in this regard.

Thanks,
Salaam Yitbarek

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 02 2000 - 00:00:00 CDT