I have string data (`ProductDescription`) that includes product names and ID numbers and need to pull the ID numbers for cross-referencing...

Data Samples:

```

Widget (1234)
Different Widget (123)
Many Widgets (2x) (4567)

```

I am trying to do this with a rather messy `CASE` statement:

```
CASE
    WHEN
        ISNUMERIC(
        SUBSTRING(
            ProductDescription
            ,CHARINDEX('(',ProductDescription)+1
            ,CASE 
                WHEN
                    CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1 > 0
                    THEN CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1
                ELSE 0 END
            )) = 1
    THEN
        SUBSTRING(
            ProductDescription
            ,CHARINDEX('(',ProductDescription)+1
            ,CASE 
                WHEN 
                    CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1 > 0
                    THEN CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1
                ELSE 0 END
            )
    ELSE 0 END

```

In cases where there is a parenthetic insertion in the product name (as in the "Many Widgets (2x)" example), I end up returning the "2x" value, which is not what I want.

**How can I look for *only* numeric values contained in parentheses and discard the contents of others?** Is there an easier/cleaner way to do the simple `CHARINDEX` work I'm currently attempting?

Thanks!