将零添加到mySQL表行中的特定位置

Question

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?

Answer 1

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.