VARCHAR2 vs. NUMBER
What is the correct circumstance to store a numeric string as VARCHAR2 vs. NUMBER? The response I have received before is to store all numeric strings as VARCHAR2 unless the intention is to manipulate the field mathematically; but no reason has been given.
Similarly, I store phone numbers in a VARCHAR2 datatype. This is typically because I have some sort of data verification...
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
routine before the phone number is inserted into the table to ensure that the value given is grammatically correct. For instance, you may decide that your phone number must be stored in the format (XXX)XXX-XXXX complete with the parentheses and the minus sign. You could opt to store this in a NUMBER datatype as XXXXXXXXXX if you want. There are no area codes in the US that have a leading zero, so you will not be losing meaning in this case. But you probably will not be performing arithmetic on these values either. It is more likely that you will be performing string manipulation instead. For instance, you might query for all phone numbers in the 555 area code (WHERE phone_num LIKE '555%'). If you were storing this value in a NUMBER datatype, a string conversion would need to take place. So a string datatype makes a little more sense here.