Re: SQL string matching

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 Thu Mar 13 2003 - 01:31:26 CET