Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: SQL string matching

Re: SQL string matching

From: Richard Wheeldon <richard_at_rswheeldon.com>
Date: Thu, 13 Mar 2003 00:31:26 +0000
Message-ID: <3E6FD15E.15FB@rswheeldon.com>


abracad wrote:
> I am trying to create a SQL query that will search a text field for a
> particular word, say 'tin'. I want to ensure it finds only the word
> 'tin', and not the characters 'tin' as part of another word, eg
> 'postings'.

You could try constructing a text index on the field. The oracle 9i syntax for this would be:

 create index i_Table1 on Table1(desc) indextype is ctxsys.ctxcat;

where you would then query it using a command such as

 select desc from Table1 where catsearch(desc,'tin',null)>0;

I'm sure you can find the equivalent for other systems. This is much more efficient but much less portable that the alternatives mentioned, and will only do keyword search,

Richard Received on Wed Mar 12 2003 - 18:31:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US