Looking to extract data between parentheses in a string via MYSQL

Question

Can someone please help. I have been searching and encountered/modified this code I am getting a 1 or 0 as a result. 1 if there is something between () and 0 if there is not. I am looking to find exactly what is between them not if there is something. So if I have a string in afield that looks like this: "ABC (989) Hello" currently I get 1 as my result I would like to get "989". Any help would be greatly appreciated.

select , OUTCNTCTNOTE regexp '[(]|\\[)]' as test from trcalls.callcoding;

Answer 1

To complete the first answer, because the third parameter passed to substr is the length of the substring, we need to subtract the index of the opening parantheses, so:

substr(columnname,instr(columnname,"(") + 1, instr(columnname,")") - instr(columnname,"(") - 1)

should do the trick

Answer 2

select substr(columnname,instr(columnname,"(") + 1, instr(columnname,")")) as temp from mytable

something close, I tested this. Please see if this helps!