Friday, 10 August 2018

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

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;



No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...