On Thu, 2001-12-06 at 12:33, Charles Hauser wrote:
> Hi,
>
> I need to store numbers which contain '0' as the first digit like '001', '01' .
>
> A little history. A DNA clone_id is denoted by '894001A01.x1'. I
> need to sort clone_id, and have broken it down into its meaningful
> components:
>
> project: 894
> plate: 001
> plate row: A
> plate column: 01
> read: x
> ver: 1
>
> CREATE TABLE clone_fasta (
> project integer NOT NULL,
> plate integer NOT NULL,
> p_row char(1) NOT NULL,
> p_column integer NOT NULL,
> read char(1) NOT NULL,
> ver integer NOT NULL,
> length integer NOT NULL,
> seq text NOT NULL,
> PRIMARY KEY (project,plate,p_row,p_column,read,ver)
> );
>
> Unfortunately, storing these numbers as integers converts 001 ->1,
> which I can't use.
>
> How does one store a number like '001'?
If you have to deal with numbers such that '001' is different to '01'
then they are not numbers: they are _text_.
Therefore: store them in a 'TEXT' field instead.
Also, if you need to sort them into numeric order nonetheless, you could
left-pad with spaces so that '001' becomes ' 001' and '01' became
' 01' and so forth.
Or you could just:
... ORDER BY int4(textfield) ...
You can even:
CREATE INDEX myindex ON myfile( int4(textfield));
to have that sort ordering supported by an index.
Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
