Oracle 9i LIKE search assistance

From: <trpost_at_gmail.com>
Date: Mon, 17 Nov 2008 17:15:02 -0800 (PST)
Message-ID: <b0601cb2-3eb3-4372-915e-6b4fab9b661a@s9g2000prg.googlegroups.com>


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. Received on Mon Nov 17 2008 - 19:15:02 CST

Original text of this message