Extract numeric part of string in MySQL

Question

I need to clean up a database containing strings like this:

<a href="/members/a/" title="a">a</a> ist der Gruppe <a href="/groups/3106/">a</a> beigetreten

or also

<a href="/members/a/" title="a">a</a> ist der Gruppe <a href="/groups/3106_123/">a</a> beigetreten

or also

<a href="/members/a/" title="a">a</a> ist der Gruppe <a href="/groups/3106_A/">a</a> beigetreten

or also

<a href="/members/a/" title="a">a</a> ist der Gruppe <a href="/groups/3106A/">a</a> beigetreten

I only need the numeric part after /groups/, so in this examples, only "3106". The number is of variable length.

Stripping the first part away is easy:

SUBSTRING(field, locate('groups/', field)+7)

but how to strip the rest away?

I think you might have to do a little bit in code, a regex would help you find what you need in MySQL but you can't extract from it. — Zach Leighton
– Zach Leighton, Commented Apr 14, 2015 at 13:21
Thanks guys. I would also appreciate an answer with a PHP-regex so I could write a small program with that. — Raphael Jeger
– Raphael Jeger, Commented Apr 14, 2015 at 13:22
Here you are: (?<=\/groups\/)[0-9]+. See regex101.com/r/zG8pL9/1 — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Apr 14, 2015 at 13:31

Wiktor Stribiżew · Accepted Answer · 2015-04-14 13:36:16Z

1

Since you are asking for a PHP regex, here you are:

(?<=\/groups\/)[0-9]+

PHP sample code:

$re = "/(?<=\\/groups\\/)[0-9]+/"; 
$str = "<a href=\"/members/a/\" title=\"a\">a</a> ist der Gruppe <a href=\"/groups/3106A/\">a</a> beigetreten"; 
preg_match_all($re, $str, $matches);

answered Apr 14, 2015 at 13:36

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

A l w a y s S u n n y · Accepted Answer · 2015-04-14 13:48:55Z

1

Try this way using REGEX of MYSQL to select only numeric of your string column value

SELECT column_name_of_db
FROM db_name
WHERE column_name_of_db REGEXP '^(?<=\/groups\/)\d+';

enter image description here

answered Apr 14, 2015 at 13:48

A l w a y s S u n n y

38.8k9 gold badges68 silver badges120 bronze badges

Collectives™ on Stack Overflow

Extract numeric part of string in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related