Searching CLOBS case insensitive
Petri
using dbms_lob.instr() but this function is case sensitive. Does
anyone know how to search case insensitively?
Howard J. Rogers
SQL> create table clobtest (
2 col1 clob);
Table created.
SQL> insert into clobtest values ('I am a CLOB');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from clobtest where upper(col1) like '%AM%';
COL1
----------------------------------------------------------
I am a CLOB
Regards
HJR
"Petri" <Pet...@hotmail.com> wrote in message
news:b4803f2d.02121...@posting.google.com...
Pablo Sanchez
news:b4803f2d.02121...@posting.google.com:
> I am trying to search a CLOB field case insensitively. I am
> currently using dbms_lob.instr() but this function is case
> sensitive. Does anyone know how to search case insensitively?
Unless you plan on having very few CLOB searches, you might want to
instead use the Intermedia/Context cartridge to do this activity.
--
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com
Petri
> Pet...@hotmail.com (Petri) wrote in
> news:b4803f2d.02121...@posting.google.com:
>
> > I am trying to search a CLOB field case insensitively. I am
> > currently using dbms_lob.instr() but this function is case
> > sensitive. Does anyone know how to search case insensitively?
>
> Unless you plan on having very few CLOB searches, you might want to
> instead use the Intermedia/Context cartridge to do this activity.
How would I use the Intermedia/Context cartridge? Do you know where I
could find an example?
John Russell
These days it's known as Oracle Text. You create a special kind of
index, then you use the CONTAINS( ) function in the query to check for
the existence of words, phrases, boolean expressions, etc.
Here are the manuals (App Dev Guide and Reference):
http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage#index-TEX
John
Vladimir M. Zakharychev
necessary parameters to it, something like this:
create index ctx_mytable on mytable (clob_col)
INDEXTYPE IS CTXSYS.CONTEXT [PARAMETERS '...']
/
and when it's online (may take quite some time if created on populated
table since DR will need to filter each CLOB and break it down into tokens,
record these tokens in its own tables and do some other things, like
theme extraction, if requested in index parameters), you query the table
like this:
select whatever from mytable
where CONTAINS(CLOB_COL,'my search term',1) > 0
(the third parameter, which is 1 here but may be any number) allows you
to identify this particular operator for result scoring:
select whatever from mytable
where ( CONTAINS(CLOB_COL,'my search term',1) > 0
and SCORE(1) > 35 )
or (CONTAINS(CLOB_COL,'ABOUT(some theme)',2) > 0
and SCORE(2) > 50 )
To find out more, please refer to interMedia Text (8i)/Oracle Text (9i)
documentation.
--
Vladimir Zakharychev (b...@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Petri" <Pet...@hotmail.com> wrote in message
news:b4803f2d.02121...@posting.google.com...
Petri
Is this type of index available in 8i or only 9i?
--
Posted via http://dbforums.com
Vladimir M. Zakharychev
>
> Is this type of index available in 8i or only 9i?
>
Yes, CTXSYS.CONTEXT index type is available on 8i. Each new release
usually adds some new functionality and support for quite a few new
document formats (if you store documents in various formats, like Word
or WordPerfect, in BLOB columns) through newer INSO filters, plus usual
bugfixes and minor internal tweaks, but basic capabilities are the same.
As I suggested earlier, refer to Oracle interMedia Text reference for more
details on this feature (that's for 8i, in 9i it was renamed to Oracle Text.)
You can find complete searchable docs here: http://tahiti.oracle.com