Isolate characters the are within brackets

Consider a text field with the following data:

Plastic Bottle (5 ml)

How would I go about i开发者_StackOverflow社区solating the text that is within the brackets so that i can use it within the context of a another statement?

Note that the text within the bracket will vary

---

Something like this should work (but it will only extract text between the first set of brackets)...

DECLARE @Input AS VARCHAR(MAX)
DECLARE @Extracted AS VARCHAR(MAX)
DECLARE @Open AS INT
DECLARE @Close AS INT

SET @Input = 'Plastic Bottle (5 ml)'
SET @Open = PATINDEX( '%(%', @Input ) + 1
SET @Close = PATINDEX( '%)%', @Input ) - 1
SET @Extracted = SubString( @Input, @Open, @Close - @Open + 1 )

PRINT @Extracted