I have a bit of SQL to extract the text between brackets at the end of a string:
WITH test_data AS
(
SELECT 'Joseph Bloggs (Joe) (THIS)' v1 FROM DUAL UNION ALL
SELECT 'Robert Holnas (Bob) (THAT)' FROM DUAL UNION ALL
SELECT 'Mary Mild (THIS)' FROM DUAL UNION ALL
SELECT 'Jack Jill (THIS)' FROM DUAL
)
SELECT v1
, INSTR(v1, '(') start_bracket_pos
, INSTR(v1, ')') end_bracket_pos
, TRIM(SUBSTR(v1,INSTR(v1, '(')+1,INSTR(v1, ')')-INSTR(v1, '(')-1)) current_label
, (LENGTH(v1) - LENGTH(REPLACE(v1, ')'))) / LENGTH(')') "ct_("
, (LENGTH(v1) - LENGTH(REPLACE(v1, ')'))) / LENGTH(')') "ct_)"
FROM test_data;
It always extracts the data from the brackets at the end of the string.
I wondered if I can change it to be able to extract the text between e.g. two sets of brackets - for the test data above, it'd be useful to be able to extract the "Joe" and the "Bob", but also the "THIS" and "THAT" at the end as well. It'd be useful to have a column containing the first bracket contents, and the 2nd set of content in another column.
Is there a switch I can use to e.g. to an INSTR() to return the position of the first open bracket, and the 2nd and so on?
Any advice much appreciated
Thanks