473,883 Members | 1,639 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Leading Zero Missing...

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
6 13790
ALTER TABLE `mijn_gebruiker s` ADD `telefoonnummer ` INT UNSIGNED ZEROFILL
NULL

<da***@scene-double.co.uk> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.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
<da***@scene-double.co.uk> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.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
I think an unsigned zero fill interger will take less storage space and will
be quicker to lookup.

:)
Wouter
"Thomas Bartkus" <to*@dtsam.co m> wrote in message
news:Y9******** ************@te lcove.net...
: <da***@scene-double.co.uk> wrote in message
: news:11******** **************@ c13g2000cwb.goo glegroups.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
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
: 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(serialnu mber,0)='0105'
But well, I like the math part a lot ;)
Wouter
Jul 20 '05 #6
"Wouter" <no*****@no.mai l.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(serialnu mber,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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1855
by: Jeff Lowry | last post by:
I'm pasing a zip code as a prameter to an Access stored procedure. In Access the parameter is a text data type. It works for non-leading zero zip codes but, apparently access (or ASP) is converting it to a value first (dropping the zero) then sending that to my SP. Even if I use cStr() to be sure the parameter is sent a string it still seems to drop the leading zero. Any thoughts? Note: It needs to be a string for canadian zip...
7
2916
by: david | last post by:
Hi, I have 2 text boxes on an ASP form. A user enters a Serial Number in TB1 such as 0105123456, presses tab to move to TB2, TB2 then displays the value of TB1 after a calculation has been done. (Based on a Serial Number range). i.e. a user enters 0105123456, TB2 then adds 'x' qty to this number depending on how many serial numbers are required.
1
12793
by: Joshua Ammann | last post by:
Hello, I'm trying to export a query containing contact information, including a field. Some zip codes have one or two leading zeros, for example, San Juan, PR (00927) and Springfield, MA (01104). When I export to a comma separated value (.csv) file, the zip code for San Juan becomes "927" and for Springfield becomes "1104". How can I prevent the leading zero(s) from being trimmed in the exported .csv file? Because the table contains...
5
20437
by: OneDay | last post by:
I've got a field that has some old data with text in it, but all forward data will be a 3 digit number. But many of the numbers are still only 2 digits. I would like to force the leading zero in the entry of the field. For example if the number 77 is entered into the field, 077 will display. How do I format to force the leading zero?
2
5676
by: chris | last post by:
Hi, I have a simple ms access application that allows you to scan barcodes in to a form which stores them in the database. The barcodes are 6 digits in length e.g. 555666 but my handheld scanner always adds a leading zero - e.g. 0555666. You can get round this problem by changing the database design for the field where this is stored from text to number format. However, when it comes to quickly doing a search for a code - CTRL + F
5
3487
by: GarryJones | last post by:
I have code numbers in 2 fields from a table which correspond to month and date. (Month, Code number) Field name = ml_mna 1 2 3 etc up to 12 (Data is entered without a leading zero)
8
4828
by: Andrew Poulos | last post by:
In my limited testing with FF 2, IE 6 and Opera 9 when I divided a positive integer, that is less than 100, by 100 I get a leading zero in front of the decimal point. For example 80/100 gives 0.8 This is what the server app is expecting a leading zero (it does indeed fail without it). Do I need to do anything to ensure the leading zero is there?
4
3223
by: bobm2005 | last post by:
Whatever format I try in Printf, an 'E' format number nearly always has a leading non-zero:- 1.2345E7 -9.3456E8 etc. Is it possible to force it (printf) always to have leading zero? Thus, the above becomes:-
6
7774
by: JimmyKoolPantz | last post by:
Task: Customer wants a script of the data that was processed in a "CSV" file. Problem: Zip-Code leading zeros are dropped Basically we have a client that has requested a custom script for each file that he has us process. He wants this in a Comma Delimited Format.
0
9944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9793
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11151
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10858
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7974
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7134
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5996
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4619
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3238
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.