In MySQL, how to extract an URL from a long text string?

2 Views

I’m learning MySQL and using phpMyAdmin to make changes in my WordPress database for a store with thousands of products.

I’m trying to do a quick update where I pull out an URL and paste it into a shortcode I want to add.

Read More

Currently, each record in my table has this somewhere within a bunch of other text. 

<input type="hidden" name="prtks" value="http://domainname/folder/filename.mp3"/>

I want to keep this and add to each record within the same field 

[sc_embed_player_template1 fileurl="url from above"/"]

It’s rather tricky as I know I can find a substring by counting the characters, but is there a way to pull out the full URL from http all the way to the final .mp3?

thanks much!

Post Views: 2

Related posts

Leave a Reply

4 comments

  1. This isn’t in MySQL, but if I understand your question correctly, you seem like you’re talking about doing this within a php file by doing something like the following:

    <?php 
    $url = '<input type="hidden" name="prtks" value="http://domainname/folder/filename.mp3"/>';
    $parts = explode('"', $url);
    echo 'URL: '.$parts[5];
?>

    This uses php explode() to break the string into an array based on ” characters.

    You can then just echo the 6th part ($parts[5])

  2. Why not just use a simple regex to extract the url ?

    $str = '<input type="hidden" name="prtks" value="http://domainname/folder/filename.mp3"/>';
preg_match_all('/b(?:(?:https?|ftp|file)://|www.|ftp.)[-A-Z0-9+&@#/%=~_|$?!:,.]*[A-Z0-9+&@#/%=~_|$]/i', $str, $match, PREG_PATTERN_ORDER);
var_dump($match[0][0]);
// string 'http://domainname/folder/filename.mp3' (length=37)

  3. <?php

function urltxt($string)
{

$regex = "/b(?:(?:https?|ftp)://|www.)[-a-z0-9+&@#/%?=~_|!:,.;]*[-a-z0-9+&@#/%=~_|]/i"; ///(http|https|ftp|ftps)://[a-zA-Z0-9-.]+.[a-zA-Z]{2,3}(/S*)?/";
preg_match_all($regex, $string, $matches);
  return $matches[0];
}

$url = urltxt($sqlresult);
echo $url[0];

//this way its more easier i guess
?>

  4. If the file extensions are all 3 chars, you could use a regular expression like http://*.{3} to find the URL, but unless they have some sort of delimiter, it would be hard to find. you could try http://*.(mp3|html|gif|png|exe|php|aif|wav) and list all possible extensions if they’re different. You could also try only listing extensions that have more than 3 chars: http://*.({3}|html|jpeg|aiff|torrent).