String formatting in MySQL

Question

Is there a printf()-like formatting in MySQL?
I couldn't find a straightforward way in the documentation.

For example, how can I make something like:

SELECT STRFORMATFUNCTIONIMLOOKINGFOR("%03d", 17)

to get 017 ?

Answer 1

You can implement this kind of function by creating a new UDF (user defined function).E.g. see http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html

Answer 2

see FORMAT() function:

mysql> SELECT FORMAT(12332.123456, 4);
returns '12,332.1235'

but it's only for formatting float numbers.

Answer 3

for your example, you could use

SELECT LPAD(17, 3, '0');

there is also

SELECT FORMAT(17, 3); -- 17.000

otherwise, UDF as mentioned above.