Menu
Is it ok to store numbers as varchar?
What's the difference between
int 123456789012 and varchar 123456789012 ?
599
As the name suggests, varchar means character data that is varying. Also known as Variable Character, it is an indeterminate length string data type. It can hold numbers, letters and special characters.
The VARCHAR data type accepts character strings, including Unicode, of a variable length is up to the maximum length specified in the data type declaration. A VARCHAR declaration must include a positive integer in parentheses to define the maximum allowable character string length.
3 Answers. The short answer is: No, it will hurt performance. The longer answer: VARCHAR fields are variable length, meaning, that the formatting of the database blocks cannot pre-account for the size of the data filling in there.
To convert a varchar type to a numeric type, change the target type as numeric or BIGNUMERIC as shown in the example below: SELECT CAST('344' AS NUMERIC) AS NUMERIC; SELECT CAST('344' AS BIGNUMERIC) AS big_numeric; The queries above should return the specified value converted to numeric and big numeric.
No, it's almost always a bad idea.
96
You can store leading zeroes to a varchar that you can't do with integer columns (eg. it is possible to have difference between 123 and 0000123). For example zip codes in some countries. However, if you need to do that, then you are really dealing with textual information that should have varchar column. For example, phone numbers or zip codes should definitely go to a varchar column.
Otherwise if you are using your data like numbers (adding them together, comparing them, etc.) then you should put it into integer column. They consume far less space and are faster to use.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
