Page 1 of 1
storing 0001 value in mysql but store as 1 why?
Posted: Wed Apr 20, 2011 2:17 am
by imrantareen
storing 0001 value in mysql but store as 1 why?
Re: storing 0001 value in mysql but store as 1 why?
Posted: Wed Apr 20, 2011 11:24 am
by McInfo
To store leading zeros, the field must have a string data type. If the field has a numeric data type, the leading zeros will be stripped off. This happens because leading zeros are insignificant to numbers. Their existence does not change the meaning of the number. However, leading zeros are significant to strings because removing a zero would change the meaning of the string.
In general, you should store the value as a number and add the leading zeros when you retrieve it. For example,
Code: Select all
SELECT LPAD(1, 4, '0') # Returns "0001"