Re: how to: oracle full text search over multiple columns
Date: Mon, 29 Dec 2003 18:48:10 -0500
Message-ID: <bsqgov$ch3a$1_at_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 (ä) 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, USAReceived on Tue Dec 30 2003 - 00:48:10 CET