mysql - Extract Data Between Parenthesis That's Always Different Lengths -

mysql - Extract Data Between Parenthesis That's Always Different Lengths -

i have run problem , cannot seem find right solution anywhere. i'd extract info column never going same length but, in parenthesis. i've tried different substr , locate statements no avail.

table: figureslog

|updatedate| |description| |2014-01-01| |(10.0.600.1) various descriptions follow| |2014-01-02| |(192.168.10.100) various descriptions follow|

i need able extract (create new table/field) containing ip addresses within parenthesis , stated, different length.

you can whole thing using locate , substr. because of how substr takes position , length, math gets little funky. illustration makes clear:

select substr(text, ip_start, ip_len) ip_addr ( select text, (locate('(', text) + 1) ip_start, (locate(')', text) - (locate('(', text) + 1)) ip_len test ) temp;

notice (locate('(', text) + 1) gets repeated. + 1 don't include parenthesis in substring. actual calculation ip_len ip_len = end_paren_pos - ip_start cannot create , select ip_start in same query.

example in action: http://sqlfiddle.com/#!2/2845e/3

mysql sql substring