How to serach in CLOB field

[Anon]

Hi!
I need a solutio to do a search case-insensitive in a CLOB Field.
I use Oracle 8 php 4.
It\'s also possible to increase a buffer dimension to save a DBMS_SUBSTR about that CLOB; now I can extract only 4.000 character
Help me please!!

[shanx24]

HI Stefano,

You could try the full-text search with the "CONTAINS" keyword (you have might need a rebuild index query after every insert/update).

Secondly, you could use the DBMS_LOB package to do an INSTR query.. I might be wrong but here is a sample of the query:

SELECT ID
FROM my_table
WHERE DBMS_LOB.INSTR(my_clob_column, 'Hi') > 0;

Thirdly, the following rather simplistic approach might help with CLOB fields as well:

STEP 1-----> $text = str_toupper($text);
STEP 2-----> $sql = "select * from my_table where toupper(my_clob) like '%" . $text . "%'";

However, remember that toupper() might escape the index on your tables, so it will adversely affect your query performance.

Just a few ideas..I'd be interested in knowing what other ideas you get. In any case, you might like to take a look at the Intermedia help - pretty useful.

Cheers
Shanx