Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extracting data from text string between brackets

user16854Feb 24 2012 — edited Feb 24 2012
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
This post has been answered by Kim Berg Hansen on Feb 24 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 23 2012
Added on Feb 24 2012
8 comments
43,428 views