Require SQL to strip data from a field

[warrenk]

http://www.website.com/product-xx11-p-16191.html

http://www.website.com/product-xx22-p-16192.html

(etc)

 

I am trying to strip out the number ie. 16191, 16192, etc) into a field.  The number is always just before the '.html' and right after '-p-'.  Any suggestions?  If it requires more then one SQL...not a problem.

 

Appreciate any help!

Warren

 

 

 

 

[shoz]

If the url is always in the pattern you posted you should be able to use the following.

SELECT
SUBSTRING(SUBSTRING_INDEX(columnname, '.', 3), LOCATE('-p-', columnname)+3) AS num
FROM
tablename

 

If the '-p-' is always the same number of characters from the beginning of the url string you can hardcode the second argument.

 

Using PHP a better solution may be

<?php
preg_match('/-p-(\d+)\.html/i', $row['columname'], $matches);
echo $matches[1];
?>

 

EDIT: query edit.