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;

score 5 · Answer 1 · answered Mar 20 '14 at 17:52

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

score 3 · Accepted Answer · answered Nov 09 '11 at 22:28

3

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

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

answered Nov 09 '11 at 22:28

r0ast3d

2,639
1
14
18

Thanks. This is very close. I get everything after the open parenthesis, Now I just need to figure out how to get rid of the close and everything after it and I will be set. – Stephen DeLillo Nov 10 '11 at 14:21
if you find the answer was usefull, please consider to accept this answer. Thank you. – r0ast3d Nov 10 '11 at 16:05
4

Here is what I ended up using: select SUBSTRING_INDEX(substr(,instr(,"(") + 1), ')', 1) as temp from ;
– Stephen DeLillo Nov 10 '11 at 18:19
@StephenDeLillo 's answer should be the accepted answer! – lightxx Nov 10 '14 at 10:51

score -1 · Answer 3 · answered Nov 09 '11 at 22:09

Mysql's regexes don't support capturing or replacing. They're purely for matching. You'd need to use regular string operations to do the actual extraction:

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

If your strings are fairly 'regular' and you don't have to worry about multiple sets of brackets in any field, then using LOCATE() and SUBSTR() would do the trick.

Looking to extract data between parentheses in a string via MYSQL

3 Answers3

Linked