Selecting Substring SQL

Question

I need to select a substring that is found between (). The starting and ending position will vary, as well as the length of the substring. I have had moderate success with the following but not 100%.

It will work for some values but not for others, it return blanks and will also change the values capitalization format, in other words if value is 'TEST' it will display as 'Test'.

SELECT SUBSTRING(columnName, CHARINDEX('(', LEN(columnName)),
CHARINDEX(')', columnName) - CHARINDEX('(',columnName)) AS INPUT
FROM tableName

Update There is only 1 set of parentheses ()

Answer 1

This will work provided you only have a single occurence of ( and ):

SELECT 
    SUBSTRING(columnName, 
              CHARINDEX('(', columnName)+1, 
              CHARINDEX(')', columnName) - CHARINDEX('(', columnName)-1)
FROM tableName

If you have values that do not have any (...) content, add this where clause:

WHERE    CHARINDEX('(', columnName) > 0 AND 
         CHARINDEX(')', columnName) > CHARINDEX('(', columnName)