Re: Storing number '001' ?

Chuck,

First off, I'd like to congratulate you on having the foresight to break
down the value into seperate, atomic, components.  Far too many novice
DBA's would have stored the whole ID as one VARCHAR, and then written a
bunch of custom functions to parse it.  The latter approach has
disastrous consequences for data integrity, so I'm thrilled that you
have made the right choice.

> project: 894
> plate:  001
> plate row: A
> plate column:  01
> read:  x
> ver:  1

> Unfortunately, storing these numbers as integers converts 001 ->1,
> which I can't use.
>
> How does one store a number like '001'?

That depends on the answer to this question:
Is the Plate number always a zero-filled 3-digit integer?

If the answer is Yes, then you can simply use the to_char function to
format the integer plate number as you wish to see it:
SELECT to_char(plate, '000');
  (warning: to_char has a bug in ver. 7.1.x that causes it to sometimes
insert a leading space, e.g. " 001" instead of "001".  To fix this, use
the Trim function)
The database will still store the integer, making for smaller disk
space, faster searching, and automatic matching if you forget to
zero-fill.

If the answer is No (i.e. the plate number is sometimes more or less
than three digits) then you'll have to store the plate number as a
VARCHAR.  In this case, you will want to create a Constraint that
prevents entry of non-numerical characters into the Plate field, and
adapt your user interface so that it zero-fills user input automatically
before saving.  E-mail me back if you need to do this.

-Josh



______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco