Re: How to search on a column without using LIKE?

In article <3429282f.2199682_at_news.erols.com>, drippel_at_perspect.com wrote:
>How about using the instr function
>
>where instr( org_name, 'stans' ) > 0
>and instr( org_name, 'widgets' ) > 0
>etc.

The use of a function, like instr(), disallows the use of an index on org_name - resulting in a table scan.

>On Tue, 23 Sep 1997 10:46:47 +1000, John Smith <jsmith_at_company.com>
>wrote:
>
>>A Developer asked me if there's a way to search within a column without
>>using LIKE.
>>
>>For example, there is a column called 'ORG_NAME' which contains a value
>>(for example):
>>
>>'STANS WIDGETS AND FLANGES LTD'.
>>
>>He wants to be able to enter a query using any of the words in the name,
>>i.e. STANS, WIDGETS, FLANGES without having to code WHERE ORG_NAME LIKE
>>'STAN%' OR WHERE ORG_NAME LIKE '%WIDGETS%' OR WHERE ORG_NAME LIKE
>>'%FLANGES%' etc.
>>
>>He did suggest having a separate table which contained a foreign key on
>>the parent table for each of the words, i.e.
>>
>>ORG_ID WORD
>>1 STAN
>>1 WIDGETS
>>1 FLANGES
>>
>>It would probably work but is a bit clunky.
>>
>>Any ideas?
>>
>>Thanks,
>>Andy Horne
>
Received on Fri Sep 26 1997 - 00:00:00 CDT