Is there a MySQL equivalent of sprintf?

Question

I have an INT field in my table, which I'd like to select as a zero-padded string. So for example, 8 would come out as 008, 23 as 023 and so on. Is this possible in a MySQL query?

Answer 1

You're looking for the LPAD function:

SELECT LPAD(23, 3, '0'); -- '023' 

Edit:

As pointed out by @Brad in the comments, you could also define the column with ZEROFILL:

`foo` INT(3) ZEROFILL 

This would always produce at least 3 digit numbers (It would zero-pad numbers less than 3 digits, and not effect those more). It's useful if you always need the numbers to come out like that (And not just in one query)...

Answer 2

If you want to achieve a minimum number of padding, without cutting the results of larger numbers, you'll need to use an IF statement.

The example below will make sure all IDs have a minimum of three digits, while letting larger IDs still pass through untrimmed.

SELECT IF(id < 100, LPAD(id, 3, 0), id)