Forcing an Intger length

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi people,
I've looked around but have not been to find an answer for my question.
I'm looking to force a length of 4 on an integer field. Example if the
field contains an 11, I would like to show it as 0011, or a 9 to show
it as 0009. What do I need to do?

Thanks in Advance.


Re: Forcing an Intger length

Quoted text here. Click to load it

I am assuming:
    a) You have an integer field.
    b) That integer field never exceeds 9999 (four digits).
    c) You want to query this integer field and have it display padded with
leading zeros as necessary to fill out to four digits.

If a, b, and c, are true - I would use the LPAD (left pad) function thus:
    SELECT LPAD(, 4, '0') As `FourDigitInt`

You will, of course, modify that to your liking.

The drawback with LPAD() is that if your field value exceeds 9999, it will
truncate (drop digits) to get back down to 4 digits.

Thomas Bartkus

Re: Forcing an Intger length wrote:
Quoted text here. Click to load it

Read about the integer display length syntax (e.g. INT(5)) and ZEROFILL
option here:

Bill K.

Re: Forcing an Intger length

Bill Karwin wrote:
Quoted text here. Click to load it

Keep in mind that these are display settings - and typically won't
apply to your applications that interface with the database (such as
PHP/Perl/C).  They generally apply to interactive query clients (such
as query browser and the mysql client).

If you need MySQL (or PostgreSQL) training, we offer a wide range of
both. ( )

Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999

Site Timeline