How to match 3rd repeated keyword in json string with my keyword in oracle sql?

Question

I'm able to match the first repeated keyword in json string using INSTR(json_string,'keyword') but trying to figure out how to match 3rd repeated keyword occuring in that json document?

Appreciate if someone can give me a clue here?

Thanks, N

Answer 1

You can pass the occurence to the INSTR function:

INSTR(string , substring [, start_position [, occurrence]])

Take a look at this example:

WITH EXAMPLE AS (
  SELECT 'abcabcabc' AS TEST_STRING FROM DUAL
)
SELECT 
  INSTR(TEST_STRING, 'a', 1, 1) AS FIRST_ENCOUNTER,
  INSTR(TEST_STRING, 'a', 1, 2) AS SECOND_ENCOUNTER,
  INSTR(TEST_STRING, 'a', 1, 3) AS THIRD_ENCOUNTER
FROM EXAMPLE

FIRST_ENCOUNTER	SECOND_ENCOUNTER	THIRD_ENCOUNTER
1	4	7

dbfiddle