Oracle | Search the text from the My_Table’s BLOB column

Query to search the text in BLOB column

SELECT * FROM MY_TABLE MT WHERE  DBMS_LOB.instr(MT.BLOB_COL_NAME,HEX_CODE_OF_TEXT_TO_SEARCH)>0;

We need to replace the HEX_CODE_OF_TEXT_TO_SEARCH by the hex code of text.

Online Hex code converter

https://codebeautify.org/string-hex-converter

Hex code of text 'ABC' is '414243'.

Now, the search query for the text 'ABC' in blob column will be like:

SELECT * FROM MY_TABLE MT WHERE  DBMS_LOB.instr(MT.BLOB_COL_NAME, '414243')>0;