asked on

Clob operation in oracle

Greetings ee warriors.

I have a text larger than 4000 characters. Nominally I would use varchar but varchar limits to 4000. In this case I need to run a near function on a clobber.

I have two words and I need to know if they are near each other....but like I said, in a clobber datatype.

Help?
Thx

slightwv (䄆 Netminder)

Create a Text index.

Then to check if they are withing 5 words of each other and word1 is LEFT of word2:

select some_column form table where contains(clob_column,'near((word1,word2),5,TRUE)') > 0;

You might also get away with some regular expressions if running 10g or above:

something like (untested) for 50 to 100 charactrers:

select some_column where regexp_instr(clobcolumn,'(word1)(.{50,100})(word2)') > 0;

Evan Cutler

ASKER

Is true left? If so, what's right? And in either direction?

slightwv (䄆 Netminder)

True for the preserve flag means the terms must appear in the order provided.

For right, reverse the words?

To say any order, use false.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Evan Cutler