CODEWITHSUNDEEP
mysqlregex
Stephen DeLillo
Stephen DeLillo

Reputation: 53

Looking to extract data between parentheses in a string via MYSQL

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;

Upvotes: 5

Views: 13777

Answers (3)

Omriko
Omriko

Reputation: 373

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

Upvotes: 6

r0ast3d
r0ast3d

Reputation: 2635

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

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

Upvotes: 3

Marc B
Marc B

Reputation: 360842

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.

Upvotes: -1

Related Questions