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.
