MySQL: Add Leading Zeros

Posted May 12, 2009 * Comments(17)

There might be many ways to force leading zeros to be displayed when string length is less than expected.

One of them is to use string function LPAD(). For example, the following query:

SELECT LPAD('1101', 8, '0');

returns ‘00001101’.

However, if string (first parameter) is longer than length (second parameter), the return value is shortened. For example:

SELECT LPAD('1101', 2, '0');

returns ’11’.

17 Comments so far

  1. fery on June 28th, 2010

    Thanks alex 🙂

  2. Marco on August 2nd, 2011

    Too me whole day to find this! Thanks!!

  3. Darren on October 20th, 2011

    Thanks for finding this alex.

  4. Amit on November 16th, 2011

    Thanks dude!!

  5. Bilal on November 16th, 2011

    Thank you

  6. ravi on March 12th, 2012

    Thanks….

  7. Ahmad on May 25th, 2012

    Thanks pal.. This is really works..

  8. latif on May 31st, 2012

    Thanks you so mutch

  9. Paul Genga on June 18th, 2012

    Thank you Sir Alex,
    This has really been a great help…

  10. alvinkool on June 30th, 2012

    Hi Alex,
    So is that anyway for if string (first parameter) is longer than length (second parameter) then it will return the first parameter instead of shortened value?

  11. Alex on July 1st, 2012

    There is always a way for a work around like this:

    SELECT LPAD(@string, GREATEST(@length, LENGTH(@string)), '0');

    or this:

    SELECT IF(LENGTH(@string) > @length, @string, LPAD(@string, @length, '0'));
  12. Deniz Gezmis on July 12th, 2012

    Cool. Thanks a bunch.

  13. DrMagento on December 12th, 2012

    Thanks so much!

  14. VH on January 13th, 2013

    lpad() – just what I was looking for.

    Thanks a bunch!

  15. Syed Rakib Al Hasan on June 17th, 2013

    perfect 🙂

  16. joshua on July 29th, 2013

    thanks!!

  17. Freddie on August 12th, 2014

    Thank you Alex for the GREATEST() example. Just what i needed!

Leave a reply