Re: Oracle 9i LIKE search assistance

trpost_at_gmail.com wrote:
> I am looking to be able to search a VARCHAR column in a table for a
> search string; Here is an example:
>
> Table: cases
>
> description
> -----------------
> airport bla bla bla
> bla bla bla airport
> bla airport bla bla
>
>
> SELECT description FROM cases WHERE description LIKE '%airport%'
>
> The problem is this only returns 1 result:
> bla airport bla bla
>
> To get all 3 results I would need to use the following queries or do
> an inline OR:
> SELECT description FROM cases WHERE description LIKE '%airport%'
> SELECT description FROM cases WHERE description LIKE 'airport%'
> SELECT description FROM cases WHERE description LIKE '%airport'
>
> I am unfortunalely restricted to Oracle 9i with this database; I see
> with Oracle 10 I can use regular expressions, but that doesn't look
> like an option for me here.
>
> Does anyone have a more efficient way to be able to search a string in
> Oracle 9i and get all 3 results regardless of where the search word
> appears in the string.

exactly which version of 9i? Have you applied all of the patch sets up to the terminal release of 9.2.0.8?

Not tested (have not seen this problem before) but you could try:

SELECT description FROM cases WHERE ' '||description||' ' LIKE '%airport%'

It is doubtful that this type of query will perform very well unless you   are using some of the Oracle Text processing, due to the inability to use a regular type of index on this column because using LIKE will result in this query doing a FTS anyway... So don't use it on large tables unless you are willing to twiddle your thumbs for a while... Received on Wed Nov 19 2008 - 20:20:01 CST