By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

Leading Zero Missing...

P: n/a
Hi,

I have an application as follows:

MySQL database Back-Eend linked to MS Access Front-End and ASP Web
Application.

I require users to enter Serial Numbers such as:

0105123567 (10 digits), the first 4 being the month and year (mmyy)

I had double as a data type, but when it came to January, all the
leading zeros are being missed off when inserted into my database.
Which datatype should I use to correct this issue ?
Appreciate your soonest reply

Thanks in advance

David.

Jul 20 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
ALTER TABLE `mijn_gebruikers` ADD `telefoonnummer` INT UNSIGNED ZEROFILL
NULL

<da***@scene-double.co.uk> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
: Hi,
:
: I have an application as follows:
:
: MySQL database Back-Eend linked to MS Access Front-End and ASP Web
: Application.
:
: I require users to enter Serial Numbers such as:
:
: 0105123567 (10 digits), the first 4 being the month and year (mmyy)
:
: I had double as a data type, but when it came to January, all the
: leading zeros are being missed off when inserted into my database.
: Which datatype should I use to correct this issue ?
: Appreciate your soonest reply
:
: Thanks in advance
:
: David.
:
Jul 20 '05 #2

P: n/a
<da***@scene-double.co.uk> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
Hi,

I have an application as follows:

MySQL database Back-Eend linked to MS Access Front-End and ASP Web
Application.

I require users to enter Serial Numbers such as:

0105123567 (10 digits), the first 4 being the month and year (mmyy)

I had double as a data type, but when it came to January, all the
leading zeros are being missed off when inserted into my database.
By declaring type "double", you are requesting the system to treat your
Serial Numbers as numbers with a floating decimal point. For such numbers,
leading zeros are meaningless and typically discarded. If you enter a
string "005" into such a field, the mysql will convert that internally to
the number 5.0 . That would be the number 5 with the decimal fixed at zero
since you didn't mention it. The leading zeros get tossed.

Even though your Serial Number may consist of only numeric digits, you won't
be involving this in addition or multiplication. Only use a numeric type
like "double" if you plan to involve it in some type of arithmetic
calculation.
Which datatype should I use to correct this issue ?


You want a text string type.
If the length of your Serial Number string is variable, you can use varchar.
Or if you *know* that they will always be exactly 10 characters (or digits!)
in length, you can call them "char" with a fixed length of 10.

In your text string (char or varchar) field, leading zeros ("0") are
significant and will be preserved.

Thomas Bartkus
Jul 20 '05 #3

P: n/a
I think an unsigned zero fill interger will take less storage space and will
be quicker to lookup.

:)
Wouter
"Thomas Bartkus" <to*@dtsam.com> wrote in message
news:Y9********************@telcove.net...
: <da***@scene-double.co.uk> wrote in message
: news:11**********************@c13g2000cwb.googlegr oups.com...
: > Hi,
: >
: > I have an application as follows:
: >
: > MySQL database Back-Eend linked to MS Access Front-End and ASP Web
: > Application.
: >
: > I require users to enter Serial Numbers such as:
: >
: > 0105123567 (10 digits), the first 4 being the month and year (mmyy)
: >
: > I had double as a data type, but when it came to January, all the
: > leading zeros are being missed off when inserted into my database.
:
: By declaring type "double", you are requesting the system to treat your
: Serial Numbers as numbers with a floating decimal point. For such
numbers,
: leading zeros are meaningless and typically discarded. If you enter a
: string "005" into such a field, the mysql will convert that internally to
: the number 5.0 . That would be the number 5 with the decimal fixed at zero
: since you didn't mention it. The leading zeros get tossed.
:
: Even though your Serial Number may consist of only numeric digits, you
won't
: be involving this in addition or multiplication. Only use a numeric type
: like "double" if you plan to involve it in some type of arithmetic
: calculation.
:
: > Which datatype should I use to correct this issue ?
:
: You want a text string type.
: If the length of your Serial Number string is variable, you can use
varchar.
: Or if you *know* that they will always be exactly 10 characters (or
digits!)
: in length, you can call them "char" with a fixed length of 10.
:
: In your text string (char or varchar) field, leading zeros ("0") are
: significant and will be preserved.
:
: Thomas Bartkus
:
:
Jul 20 '05 #4

P: n/a
On Tue, 04 Jan 2005 23:51:12 +0100, Wouter wrote:
I think an unsigned zero fill interger will take less storage space and
will be quicker to lookup.


Less storage space? Granted.
Quicker to look up? How much speed can you possibly gain?

David mentions that his serial number leads off with the month and year
"mmyy". What if he wants retrieve all Aug, 2004. If he he sticks with a
proper string type field he can
WHERE SerialNumber LIKE '0804??????'

I know you might hack a work around for that with an integer field but you
just blew away any possible speed advantage you thought you had by forcing
an integer type. It *should* be a string type because you will want to
perform string type operations with it. Not integer math operations.

Why fight the system?
Thomas Bartkus

Jul 20 '05 #5

P: n/a
: I know you might hack a work around for that with an integer field but you
: just blew away any possible speed advantage you thought you had by forcing
: an integer type.
Are you shure about that?
I though LIKE lookups are very slow.

well, if the first part is a date (I did not read that from his post the
first time).
why don't just split the serial in 2 parts and save one as date and one as
interger?

In that way, the program will also work after 2099 :-D

Or a fixed floating point after 4 digits put a .
DECIMAL(4,6),

Then one can lookup all things with a special date by truncation.
format(serialnumber,0)='0105'
But well, I like the math part a lot ;)
Wouter
Jul 20 '05 #6

P: n/a
"Wouter" <no*****@no.mail.for.me> wrote in message
news:cr**********@news.tudelft.nl...
: I know you might hack a work around for that with an integer field but you : just blew away any possible speed advantage you thought you had by forcing : an integer type.
Are you shure about that?
I though LIKE lookups are very slow.
If that field is indexed, it would be very fast. Integer operations are
always fastest but that is a moot point. No one was asking how to speed up
the query.
well, if the first part is a date (I did not read that from his post the
first time).
why don't just split the serial in 2 parts and save one as date and one as
interger?

In that way, the program will also work after 2099 :-D

Or a fixed floating point after 4 digits put a .
DECIMAL(4,6),

Then one can lookup all things with a special date by truncation.
format(serialnumber,0)='0105'


What we have here is a basic clash of programming philosophies!
Mine is:
If you plan to do string style manipulations then let the system work for
you by using a string type.
If you plan to do arithmetic manipulation then let the system work for you
by using a numeric type.

I would advance that philosophy to novices - especially to someone who is
struggling with the initial question about why leading zeroes vanish from
floating point fields.
But well, I like the math part a lot ;)

Then you will like to use numeric types a lot :-)

Thomas Bartkus
Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.