0

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

Naseer
  • 127
  • 10

1 Answers1

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

VvdL
  • 2,799
  • 1
  • 3
  • 14
  • ok thank you..& final thing here.. how to filter and, &, or, keywords within sql to match the keyword. I have 'and' in keyword, '&' in json string. – Naseer Oct 31 '22 at 11:43