I have a mySQL table with alot of links like this:
id - link
1 | index.php?video=12
2 | index.php?video=345
3 | index.php?video=6789
4 | index.php?video=123&other=variable
5 | www.site.com/index.php?video=456&other=variable
One link per text row. I would like to add zeros before the numbers but it has to be nine numbers in total. so video=12 would be video=000000012 and video=6789 would be video=000006789.
Is there some way to acheive this by using SQL query?
EDIT: the solution tombom submitted worked fine but what if I have links that don't have the video=x variable?
UPDATE yourTable
SET `link` = REPLACE(`link`, SUBSTRING(`link` from LOCATE('=', `link`) + 1), RIGHT(CONCAT('000000000', SUBSTRING(`link` from LOCATE('=', `link`) + 1)), 9))
See it working live here in an sqlfiddle.
UPDATE:
What if I have some links with more url variables? like: index.php?video=123&play=1&search=hello
That's a bit trickier, but here you go:
UPDATE yourTable
SET `link` = replace(`link`, substring(`link`, locate('=', `link`) + 1, ABS(locate('&', `link`) - locate('=', `link`) - 1)), right(concat('000000000', substring(`link`, locate('=', `link`) + 1, ABS(locate('&', `link`) - locate('=', `link`) - 1))), 9))
Or you can do it a bit shorter like this:
UPDATE yourTable
SET `link` = , CONCAT(SUBSTRING_INDEX(`link`, '=', 1),'=', LPAD(SUBSTRING(`link` from locate('=', `link`) + 1),9,'0'))
See sqlfiddle.
UPDATE table1 SET `link` = CONCAT(SUBSTRING_INDEX(`link`, '=', 1),'=', LPAD(SUBSTRING_INDEX(`link`, '=', -1),9,'0'))
See sqlfiddle