As a thought, are you ALWAYS having that format? What I mean is an open bracket, a single character, a closing bracking a space, the name, a space a - and a number?
The reason I ask is if that is ALWAYS the case, you could use something like:
RIGHT is being used to grab all of the characters except the first 4 which gets MOST of the string. LEFT is then taking that string and grabbing all of the characters until the - character. With my testing this seems to work.
IF my assumptions are incorrect, you are going to need to adjust that "- 10" part because that is only valid if the string is a constant length. To use your query:
SELECT LEFT(SUBSTRING([AGENT],CHARINDEX(')', [AGENT])+1, LEN([AGENT])-CHARINDEX(')', [AGENT])),CHARINDEX('-',[AGENT])-CHARINDEX(')',[AGENT])-2)
That - 2 at the VERY end of the LEFT is to handle the spaces as there is a space after the ) and a space before the - which we don't need to capture.
One thing that I would do though is to put all "magic numbers" (the +1 and -2 in your query or the +4 and -4 in my query) into variables so you can see what they are being used for and why they are needed. In mine, it would be a "RemoveLeadingChars" (at least that is what I'd call it) and give it a value of 4. In yours, the 2 would be "RemoveSpaces" and the 1 would be "RemoveSpace". Not the best names, but I think it works, no?