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.

John

<john.carbonette [at] gmail.com> wrote in message
news:1149003238.785532.70060 [at] y43g2000cwc.googlegroups.com...
> 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?

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({field}, 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

john.carbonette [at] gmail.com wrote:
> 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?

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

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Regards,
Bill K.

Bill Karwin wrote:
> john.carbonette [at] gmail.com wrote:
> > 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?
>
> Read about the integer display length syntax (e.g. INT(5)) and ZEROFILL
> option here:
>
> http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

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. (http://www.otg-nc.com)

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