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: Lennart Jonsson <lelle2_at_bonetmail.com>
Date: Sun, 09 Mar 2003 11:55:18 +0100
Message-ID: <b4f6j1$1us5k9$1@ID-167942.news.dfncis.de>


On Sat, 08 Mar 2003 16:03:41 +0000, 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'.
>
> I can create a query such as:
> SELECT desc
> FROM Table1
> WHERE desc LIKE '% tin %' OR desc LIKE 'tin %' OR desc LIKE '% tin' OR
> desc='tin'
>
> But is there a shorter or more efficient way of accomplishing the same
> objective?
>
> Thanks in advance

It dont qualify as shorter or more efficient, but it is a different approach:

select

        distinct desc
from

	table1, 
	(values ('',' '),(' ',''),(' ',' ')) as tmp(prefix, suffix) 
where 
	locate(tmp.prefix||'tin'||tmp.suffix, desc)>0 or desc='tin'


/Lennart Received on Sun Mar 09 2003 - 04:55:18 CST

Original text of this message

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