...

View Full Version : Mysql number problem



fionaom87
02-23-2009, 02:20 PM
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.

Thanks

djm0219
02-23-2009, 03:04 PM
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.

Fumigator
02-23-2009, 08:09 PM
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.



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

Old Pedant
02-23-2009, 08:36 PM
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

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.

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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum