Re: how to: oracle full text search over multiple columns

From: Matthias Wirtz <Matthias.Wirtz_at_epost.de>
Date: Mon, 29 Dec 2003 18:48:10 -0500
Message-ID: <bsqgov$ch3a$1@ID-151394.news.uni-berlin.de>

"joes" <joes_at_bluewin.ch> wrote
news:26760a3e.0312241445.7bf7be55_at_posting.google.com...

> I am looking for a way to realize a full text search on a whole table
> in oracle 8.1.7 . I found "catsearch()" and "contains()" features. But
> it seems that these only supports the lookup for a single column. So
> exists there way to realize a full text search over multiple columns ?

Maybe I didn't get the point of the question but perhaps the answer is to use an 'or' in the where clause:

scott_at_DEV> create table full_text_search (col1 varchar2(32), col2 varchar2(32), col3 varchar2(32));

scott_at_DEV> insert into full_text_search (col1, col2, col3) values ('one', 'two', 'three');

scott_at_DEV> insert into full_text_search (col1, col2, col3) values ('ten', 'eleven', 'twelve');

scott_at_DEV> select * from full_text_search where col1 like '%g%' or col2 like '%f%' or col3 like '%h%';

COL1                             COL2                             COL3
-------------------------------- -------------------------------- ----------
----------------------
one                              two                              three

> second Question I have is about, does Oracle supports an automatic
> translation of special characters suring the search ? for instance
> german "umlaute" so that the a umlaut (&auml;) is automatic trnsalted
> into 'ae' or 'a'. Or that the french characters are translated into
> 'e' ?

To my knowledge there is no built in methode to perform the transformation you are asking for. But fortunately there are only three Umlaute in german so by using a lookup method you are calling from the sql statement should not be much work. Take a look at the REPLACE function:

scott_at_DEV> insert into full_text_search (col1, col2, col3) values ('vier', 'fünf', 'sechs');

scott_at_DEV> select replace(col2,'ü','ue') from full_text_search;

REPLACE(COL2,'Ü','UE')

---

two
eleven
fuenf

scott_at_DEV> spool off

--
Matthias Wirtz  -  Norfolk, USA

Received on Mon Dec 29 2003 - 17:48:10 CST