Search code examples
sqlsql-serversubstringreverse

SQL Server - Select integer that falls between brackets ( )

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.

Solution

  • 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)