Re: VARCHAR vs NUMBER
Date: 1995/10/26
Message-ID: <46o301$bt_at_crissy.ge.com>#1/1
smurray_at_booz.bah.com (Murray Shane) wrote:
>Q: Is there an advantage in storing a "number" as a NUMBER data
>type given that I don't need to
>do any mathematical operations on this number field?
>
>The scenario I have is this: I want to store a case tracking number
>that is currently a NUMBER
>data type. What if this were a VARCHAR(x) data type? I could then
>use letters and numbers.
>
>Is there a performance issue on sorting, finding, etc.?
>
>--
>--Shane Murray (offsite)
>
>*** Make sure to reply to ***
>*** smurray_at_booz.bah.com ***
I believe there is some storage savings using NUMBER vs. VARCHAR2, but this hardly a consideration any longer. More important is how you want your sort to return and the format of your "number". VARCHAR2 would indeed allow you to use other characters and leading zeros if you want (i.e. 000783). Just be aware that VARCHAR2 sorting will fall into this pattern:
VARCHAR2_NUMBER_COL
1
10
100
101
..
11
12
..
2
20
200
..
21
22
..
3
30
which you probably wouldn't want. You can get around this by storing leading zeros, or in your query use
ORDER BY TO_NUMBER( VARCHAR2_NUMBER_COL ) iff *all* values in the column have only numeric characters. Received on Thu Oct 26 1995 - 00:00:00 CET