Ugly, I know but it could get you closer to the truth 🙂

declare @t table (s varchar(100))

insert @t (s)

select 'ANALYSIS RECORDS ON 1/1/2008 (APPROX 100 PPS)'

union all select 'REVIEW MANUAL (750+ PAGES)'

union all select 'ANALYSIS RECORDS (APPROX 108 PPS)'

union all select 'REVIEW MANUAL (78+ PAGES)'

select case when patindex('%PPS)%',s) > 0 then substring(s, patindex('% PPS)%',s) -

patindex('%[^0-9]%',

reverse(

left(s, patindex('% PPS)%',s)-1 )

)

) + 1 ,len(s) - patindex('% PPS)%',s)-1

)

when patindex('%PAGES)%',s) > 0 then replace(substring(s, patindex('% PAGES)%',s) -

patindex('%[^0-9+]%',

reverse(

left(s, patindex('% PAGES)%',s)-1 )

)

) + 1 ,len(s) - patindex('% PAGES)%',s)-2

),'+','')

else '-1' end , s

from @t