Get distinct RegEx Matches on mySQL

Question

I need some help from the RegEx and SQL nerds. ^^

I've a comment field in a table, which content looks like this:

What I need is a DISTINCT list of all user names - eg.

b.willis
p.fox

g.clooney

CREATE TABLE IF NOT EXISTS `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`comment` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `comments` (`id`, `comment`) VALUES
(1, 'test [name](p.fox)[/name]'),
(2, 'another test [name](p.fox)[/name]'),
(3, 'lalala [name](b.willis)[/name]'),
(4, 'lulu [name](g.clooney)[/name]');

Thx!

Why would you not have a separate field in the table for the name? — Zlatin Zlatev, May 13 '14 at 12:06
It's some kind of plugin for an existing system, which creates this 'trash' - so I've no choice... — Petra, May 13 '14 at 12:11

score 0 · Accepted Answer · answered May 13 '14 at 12:10

0

One way to approach this is using substring_index(). Assuming each comment has this structure and only has one name:

select distinct substring_index(substring_index(comment, '[name](', 2), ')[/name]', 1) as name
from comments

answered May 13 '14 at 12:10

Gordon Linoff

1,198,228
53
572
709

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

See the thread MySQL - Return matching pattern in REGEXP query

Also check How to do a regular expression replace in MySQL? and the referenced UDF implementation of RegExp. Take a look on the implementation of the UDF REGEXP_SUBSTR

edited May 23 '17 at 12:27

Community

1
1

answered May 13 '14 at 12:11

Zlatin Zlatev

2,888
1
22
30

Get distinct RegEx Matches on mySQL

2 Answers2