How do I use SUBSTRING and CHARINDEX in SQL to return only the right part of a field contained within brackets if multiple brackets are in the field?

Question

I have a column of data that contains varchar data and I've been trying to return only the section contained with brackets using CHARINDEX and SUBSTRING :

column data: 'this is an (example)'

I use the following code to return 'example' from the above data:

SELECT SUBSTRING (column,  CHARINDEX('(', column)+1 ,   CHARINDEX(')', column)- CHARINDEX('(', column)-1 )

This works fine, however, in some instances, the data in the field has multiple occurrences of data between two brackets:

- 'this (is) an (example)'

This means that my code above returns 'is'. In my data, what I want to return is the data in the right most set of brackets:

• 'this (is) an (example)' - I would want to return 'example'
• 'this (is) some (text)' - I would want to return 'text'
• 'this (is) definitely (not) a (number)' - I would want to return 'number'

etc

Answer 1

This is one way to go also: Here is the DEMO

select reverse(
          substring(
             reverse(str_col)
             , CHARINDEX(')', reverse(str_col))+1
             , CHARINDEX('(', reverse(str_col))-2 
          )
       )
from test;