Re: Fulltextsearch How to do

From: Harlin Setiadarma <harlins_at_bigfoot.com>
Date: Mon, 06 Dec 1999 09:46:47 +0700
Message-ID: <vg7m4sslq1n7mjfi00d9kf9s5pd9veeopr@4ax.com>

Here's how :

• create a clob typed column in your table.
• index that column using interMedia Text Index (Oracle 8i) ex. : create index clob_imedia on your_table ( your_clob_column ) indextype is ctxsys.context {optional parameter clause...}

That is for text indexing. You can also index binary such as PDF. WORD, RTF, etc....
All you have to do is to create a BLOB typed column. And import those binary file to your BLOB column using one of methods that you can see in documentation. Then you have to index that BLOB column using create index syntax like previous.

To search the text in your CLOB/BLOB column use this sql : select your_blob_column, SCORE(1) --SCORE clause to display search   from your_table -- result score   where CONTAINS(your_blob_column, 'SEARCH_EXPRESSION', 1)

The number 1 in CONTAINS is to bind its search score to query SCORE() result.

The SEARCH_EXPRESSION can include :

- Logical : AND, OR
- words
- stemmed word eg: $digging can be used to find word dig, digged,
                                digger, etc (The root word)
- soundex eg. !bang can result to find the word bank

- thesaurus (require thesaurus file installation) - themes eg. (I forgot the symbol) if you search computer you will

                    get hardware, software, brainware search result
                    too
- about query eg. ABOUT(oracle intermedia text index) will give you
                           more precise search result.

- and so on. (There so many features that I can't describe all)

You can also specified your language setting and case sensitivity in your index parameters.

Please read the documentation to be more precise about interMedia Text (formerly Context in Oracle 8.0) index.

Hope this information helps.

Regards,
Harlin Setiadarma
PERKASA PILAR UTAMA, PT
Technical Division

On Fri, 3 Dec 1999 10:42:18 -0000, "Bernd" <bschloemer_at_t-online.de> wrote:

>Hallo,
>
>I have to migrate an MS-SQL-Server DB to Oracle 8.X. I have only a problem
>with the fulltextsearch in MS-SQL.
>In MS-SQL the use an SQL-Statment like "select * from table where FREETEXT
>(field,'searchtext')".
>
>Is there also an way like this for Oracle. Does anybody have an example ?
>
>
>Regards Bernd
>

• Harlin

You see but you do not observe,
You hear but you do not observe.
-- Thomas Sherlock Scott Holmes Received on Sun Dec 05 1999 - 20:46:47 CST