SQL Server - Select integer that falls between brackets ( )

Question

HI, how can i select the Integer that falls between a pair of ( ) begining from the right of a cell? Reason being, there might be another pair of brackets containing characters

and what if some records are w/o close brackets for some reason..

e.g. 
Period | ProgrammeName             |  
Jan    | ABC (Children) (30)       |
Feb    | Helloworld (20T (20)      |

result: 30 20

i have this script,

select Period, ProgrammeName, substring(ProgrammeName,(len(ProgrammeName) - (patindex('%(%', Reverse(ProgrammeName)))+2),(len(ProgrammeName)-1)) from Table

but it only displays 30) 20)

i have been manipulating it so that it doesn't extract ')', but can get the expected results.

Answer 1

Your database needs normalisation.

Whatever that (30) and (20) represent should be in their own column.

Answer 2

Hopefully this is a migration task. You could try it with a RegEx:

http://blogs.msdn.com/b/sqlclr/archive/2005/06/29/regex.aspx

EDIT: or this blog post

http://justgeeks.blogspot.com/2008/08/adding-regular-expressions-regex-to-sql.html

Answer 3

Quick and dirty if you want to prep the data for normalization;

select substring(fld, patindex('%([0-9]%)', fld) + 1, len(fld) - case patindex('%([0-9]%)', fld) when 0 then 0 else patindex('%([0-9]%)', fld) + 1 end)