Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Feb 2009
    Posts
    13
    Thanks
    0
    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.

    Thanks

  • #2
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,306
    Thanks
    4
    Thanked 205 Times in 202 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

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    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

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,495 Times in 4,459 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •