How to identify a particular string in LOB (XML_BLOB) column data ?

Dear Friends, 

You might have come across the situation to check whether a particular string is existing anywhere within the LOB column value. Here we go with the solution.


Say for example:-

Consider the table I_TRANSACTION  which has the below columns 

I_TRANSACTION_NO    NUMBER, 
XML_BLOB                   CLOB, 
DOMAIN_NAME            VARCHAR2


And the value for XML_BLOB seems like below.

<TenderOffer><Shipment><ShipmentHeader><ShipmentGid><Gid><DomainName>DUMMY</DomainName><Xid>SHIP12345</Xid></Gid></ShipmentGid><ShipmentRefnum><ShipmentRefnumQualifierGid><Gid><Xid>BM</Xid></Gid></ShipmentRefnumQualifierGid><ShipmentRefnumValue>.........
........
........
......../Gid></ShipmentGid></ShipmentStatus></Shipment><ExpectedResponseDt><GLogDate>20170216121036</GLogDate>


Requirement:-

If you want to identify whether the particular key value  "SHIP12345"  is available or not ?  

DBMS_LOB.INSTR function can be used to achieve this. 


Oracle Syntax :-

There are four parameters to be passed as below.
DBMS_LOB.INSTR   (   COLUMN_NAME,   VALUE_TO_SEARCH,    START_POSITION_SEARCH,  NTH_TIME_OF_OCCURRENCE ) 

Example Query:-

SELECT  DBMS_LOB.INSTR(   XML_BLOB,   'SHIP12345',   1,  1 ) 
FROM I_TRANSACTION
WHERE I_TRANSACTION_NO = 12345678

Output :-

If it returns  "0" , then the  "VALUE_TO_SEARCH" is NOT available in XML_BLOB for the transaction_no

If it returns value greater than "0" , then the "VALUE_TO_SEARCH"  is  Available in XML_BLOB for the transaction_no. Actually, it returns the position of the value in XML_BLOB.  if the return value is "72", it means the value is available from 72nd character of the XML_BLOB.