mysql pcre/posix regexp?

Question

i have a column with a user's profile, i would like to extract hyperlinks from that profile.

example: user="bob", profile="this is my profile, look at http://google.com and http://yahoo.com and my myspace at http://myspace.com/users/mlkgj"

should return:
user | link
"bob" "http://google.com"
"bob" "http://yahoo.com"
"bob" "http://myspace.com/users/mlkgj"

Answer 1

I don't know that mySQL has those capabilities, but php that will mimic it is:

$SQL = "SELECT user,profile FROM table";
//run your select

while($rs=fetchRow()){
//change that function however your database abstraction works
$name = $rs['user'];
$regExp="/http://.*\w/";
$matches=array();
preg_match_all($regExp,$rs['profile'],$matches);
foreach($matches as $url){
echo "$name $url
";
}
}
?>