Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
Thread: Mysql number problem
02-23-2009, 01:20 PM #1
- Join Date
- Feb 2009
- Thanked 0 Times in 0 Posts
Mysql number problem
Hello, im having a small but very annoying problem.
when i enter a number into my database: e.g. 087431122
when it is entered into the db the 0 is cut off and looks ike 87431122. Same occurs when i am selecting the number from the db the 0 is cut off.
any help would be great.
02-23-2009, 02:04 PM #2
- Join Date
- Aug 2003
- Wake Forest, North Carolina
- Thanked 204 Times in 201 Posts
If the column you are storing it in is defined as a numeric column, INT, SMALLINT, BIGINT etc that is to be expected since a leading zero has no significance. If you need it stored without alteration you could use a VARCHAR or TEXT type of column to store it.
Dave .... HostMonster for all of your hosting needs
02-23-2009, 07:09 PM #3
- Join Date
- Dec 2005
- Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
- Thanked 637 Times in 625 Posts
Mmm, I would disagree with storing your numbers as varchar or char. Sure, you get your leading zeros, but you lose the ability to perform column functions such as sum, average, min, and max, and sorting can become problematic. Also, validation become an issue, as the column now accepts non-numeric data which may break applications and cause grief down the road.
Instead, you can force leading zeros using the repeat() function.
Code:SELECT REPEAT('0', 10 - LENGTH(RTRIM( CAST(numeric_column AS CHAR(10)) ))) || RTRIM(CAST(numeric_column AS CHAR(10))) AS column_with_leading_zeros FROM table_name
02-23-2009, 07:36 PM #4
Hmmm...let's see...phone numbers. social security numbers. They *CAN* be stored as numbers (because they don't have leading zeroes) but traditionally aren't.I would disagree with storing your numbers as varchar or char. Sure, you get your leading zeros, but you lose the ability to perform column functions such as sum, average, min, and max, and sorting can become problematic
The reason they work fine as text: Because by nature we do *NOT* do SUM or AVERAGE or MIN or MAX or ORDER BY on such "numbers". There is is no natural "sequence" to such identifiers.
So... I think it's a bit too strong to say that numbers should never be stored as TEXT.
So if the original poster has (say) a product code that is *ALWAYS* exactly 9 digits long and his 087431122 was just one example...well, maybe text really is the best choice.
Don't get me wrong; 90% or more of the time I would choose to use a numeric field, as Fumigator suggested, but clearly not 100% of the time.