How to pull number code from strings buried in parentheses?
If the data you need is always at the end of the string, would something like this work?

declare @test varchar(200);

set @test= 'Many Widgets (2x) (4567)';

select substring(@test,
                 (len(@test) - charindex('(',reverse(@test))) + 2,
                 charindex('(',reverse(@test)) - 2
                );


We can reverse the string, find the first open paren (which is really the last open paren), and go from there.