Hello,
I'm trying to programmatically pre-process and inspect SELECT statements that a user may enter as free-text. The objective is to (i) separate out each optimizer hints and (ii) count how many are they in the list.
In the example below, we have a single line of hints. I need help with the delimiter part of the REGEXP pattern to ensure we separate out each hint properly and then determine a count of them.
Just to demonstrate this, I chose a space character as the delimiter but this is obviously incorrect as some hints may contain one or more spaces.
SELECT
LEVEL AS element_num,
UPPER(TRIM(REGEXP_SUBSTR(raw_text, '[^ ]+', 1, LEVEL))) AS single_element
FROM (
SELECT 'CONTAINERS(DEFAULT_PDB_HINT=''NO_PARALLEL'') parallel_index(8) PARALLEL(8) INDEX_COMBINE(e IX1 IX2) INDEX(p IX3) ORDERED' AS raw_text
FROM dual
)
CONNECT BY LEVEL <= REGEXP_COUNT(raw_text, '[^ ]+')
;
The above produces this result:-
However, the correct result should be 6 hints.
Thanks in advance for your help.