Looking at extracting data between brackets in a string through MYSQL

Question

Someone can help. I searched and discovered / modified this code, I get 1 or 0 as a result. 1 if there is something between () and 0 if not. I am looking to find that which is not between them, if there is something. Therefore, if I have a line in the field that looks like this: “ABC (989) Hello,” I am currently getting 1 as my result, I would like to get “989”. Any help would be greatly appreciated.

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

Answer 1

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

something close i checked it. See if it helps!

Answer 2

To complete the first answer, since the third parameter passed to substr is the length of the substring, we need to subtract the index of the open brackets, so:

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

gotta do the trick

Answer 3

Mysql modes do not support capture or swapping. They are only for compliance. To perform the actual extraction, you must use regular string operations:

 SELECT ...string stuff here... FROM yourtable WHERE OUTCNTCTNOTE regexp .... 

If your lines are pretty “regular” and you don’t have to worry about multiple sets of brackets in any field, then use LOCATE() and SUBSTR() would do the trick.