weidong_77
asked on
oracle blob text search
Is it possible to search through blob text using sql statement?
I can do select * from $table where f1 like '%foo%' if the f1 is varchar, how about f1 is a blob? Any counter part for this?
I'm writing my code for mysql also, so I'll need a generic way. No thanks for intermedia text search.
I can do select * from $table where f1 like '%foo%' if the f1 is varchar, how about f1 is a blob? Any counter part for this?
I'm writing my code for mysql also, so I'll need a generic way. No thanks for intermedia text search.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dbms_Lob.Instr(lob_field, rawtohex('test') ) actually works. But it's case sensitity, anyway to make it otherwise?
I'm also looking at make it clob, but I'm having mutiple charsets, will this fit in CLOB? If it is, what charset should I use? How about conversion from blob to clob?
I'm also looking at make it clob, but I'm having mutiple charsets, will this fit in CLOB? If it is, what charset should I use? How about conversion from blob to clob?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Although it doesn't solve my problem, I found it has been helpful and enlightening in indirect way. It actually triggered me some other way to do it, especially comment from benpung. Although his comment is not an answer at all.
ASKER
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at line 1
As for mysql, like "%foo%" works perfectly on varchar or lob alike.