472,126 Members | 1,547 Online

Forcing an Intger length

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?

John

May 30 '06 #1
3 1814
<jo*************@gmail.com> wrote in message
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
May 30 '06 #2
jo*************@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?

option here:

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

Regards,
Bill K.
May 30 '06 #3
Bill Karwin wrote:
jo*************@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?

option here:

http://dev.mysql.com/doc/refman/5.0/...ric-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

Jun 5 '06 #4