Extract right string between last two parentheses

Question

I need to extract the values inside the last parentheses of each row. Column Name is what I have and column ID is what I need.

Notice that row 2 has two set of parentheses and that one row has no parentheses.

I tried using this script:

SUBSTRING(name,CHARINDEX('(',name)+1 ,CHARINDEX(')',name)-CHARINDEX('(',name)-1) 

The script returns error message Invalid length parameter passed to the LEFT or SUBSTRING function. because of the the third row has no parentheses.

Answer 1

For your sample data, this should do what you want:

select replace(right(name, charindex('(', reverse(name) + '(') - 1), ')', '')