Oracle Database Discussions

Hi,

I have a need to find the location of second single quote in a string.

Below query works fine for a string without single quote. It gives me the location of word 'HIER' for 2nd occurrence.

select instr('HIER A HIER B','HIER',2) from dual

I want to do the same with single quote. I am trying with the below query.

select instr('HIER A '' HIER B ''',chr(39),2) from dual

But it always gives me the location of first occurrence of single quote and not the second.

Any idea about this issue..?

Thanks