CODEWITHSUNDEEP
mysqlregex
Raphael Jeger
Raphael Jeger

Reputation: 5232

Extract numeric part of string in MySQL

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?

Upvotes: 0

Views: 748

Answers (2)

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

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

Upvotes: 1

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626728

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);

Upvotes: 1

Related Questions