Oracle Database - DBMS_LOB Package

About

This function returns the matching position of an occurrence of a pattern in the LOB. If the function returns 0, it signify that the pattern was not found.

Syntax

SELECT DBMS_LOB.INSTR(myBlob, 'myPattern', myStartOffset, myStartOccurenceNumber),
FROM myTableWithALob

where:

• myBlob is of a CLOB of a BLOB column
• myPattern is a character string (CLOBs) or a RAW bytes (BLOBs)
• myStartOffset is the absolute offset in characters (CLOBs) or bytes (BLOBs) at which the pattern matching is to start. (default: 1)
• myStartOccurenceNumber is the occurrence number (default:1)

Example

The following SQL

SELECT DBMS_LOB.INSTR(physical_log.query_blob, 'DI_OBIEE_AIRLINE_AGG.SA_'),
FROM s_nq_db_acct physical_log

return the position of the first occurrence of DI_OBIEE_AIRLINE_AGG.SA_ :

1018