Adding zeros to specific place in mySQL table row

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?

look at regex replace http://stackoverflow.com/questions/1755408/mysql-regex-replace — Waygood, Apr 25 '13 at 10:27
Have a look at [MySQL regex-replace](https://launchpad.net/mysql-udf-regexp) — Aleks G, Apr 25 '13 at 10:28
it would be a better idea to only store the ID number (12 etc) and then generate the link when you are outputting it. You could then format this pretty easy in any programming language you use — Andrew, Apr 25 '13 at 10:32

fancyPants · Accepted Answer · 2013-04-26T06:52:19.623

3

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.

edited Apr 26 '13 at 06:52

answered Apr 25 '13 at 10:32

fancyPants

50,732
33
89
96

What if I have some links with more url variables? like: index.php?video=123&play=1&search=hello – Mikael Frôding Apr 25 '13 at 13:41
That worked but I found I hade some links in there that don't have the video=x variable at all. Those links got screw up. How do I not apply the query to them? – Mikael Frôding Apr 26 '13 at 08:32

Amir · Answer 2 · 2013-04-25T11:29:29.073

0

UPDATE table1 SET `link` = CONCAT(SUBSTRING_INDEX(`link`, '=', 1),'=', LPAD(SUBSTRING_INDEX(`link`, '=', -1),9,'0'))

See sqlfiddle

edited Apr 25 '13 at 11:29

answered Apr 25 '13 at 11:23

Amir

4,089
4
16
28

What if I have some links with more url variables? like: index.php?video=123&play=1&search=hello – Mikael Frôding Apr 25 '13 at 13:40

Adding zeros to specific place in mySQL table row

2 Answers2