Syntax for Oracle SQL INSTR function when the pattern contains hex characters

Question

I'm trying to write a query in Oracle SQL to access a particular substring in a CLOB field.

To use the SUBSTR function, I need to come up with a starting point in the CLOB using the INSTR function.

The CLOB contains X12 data that is delimited with hexadecimal characters.

To find the starting offset for my substring, I'd like to do something like this...

INSTR(my_clob, '\0x1CGE\0x1D', 1, 1)

..where the second parameter is my "pattern", being a hex 1C, followed by the letters G and E, followed by a hex 1D.

I've tried everything I can find via Google and nothing has worked so far. Either it fails with invalid syntax, or returns nothing at all.

For DBMS_LOB.INSTR(), the pattern is not the same as the pattern used for LIKE. It appears that it will only take a string of characters, not a pattern matching expression, though I'm not 100% sure what it will accept.

Any ideas?

Answer 1

If I understood you correctly the \0x1C represents a single character with the ASCII value of 0x1C (decimal 28), right?

In that case try:

INSTR(my_clob, CHR(28)||'GE'||CHR(29), 1, 1)