2

exist any way to store in database a numbers with or without decimal point? The user can store to database numbers like 10 and 10.1 - for storing numbers in MySQL I use data type column decimal, so for this example, in the database will be stored 10.00 and 10.10.

But I will need to store the exact form of number by user - so in this case 0 10 and 10.1.

Is possible to solve it on database level or I have to edit on application level?

user1946705
  • 2,858
  • 14
  • 41
  • 58
  • 4
    This is not a storage problem, but a display problem. As such it has nothing to do with the database. And what would you want to do if the user actually entered **10.0**? – Jon Sep 07 '11 at 09:18
  • Show the code you use to display the stored value, that's where your problem is. – Jon Sep 07 '11 at 09:29
  • I am able to solve it in code, but I interested if is possible to do it some more clear way - eg. direct in database. It looks probably not, so I have to write an own function for it. – user1946705 Sep 07 '11 at 09:37

3 Answers3

1

make use on case

case format(@number,0)=@number 
  when 1 then format(@number,0) 
  else 
  case format(@number,1)=@number 
    when 1 then format(@number,1) 
    else @number
  end 
end

or if

if (format(@number,0)=@number, format(@number,0),
  if (format(@number,1)=@number, format(@number,1), @number))
ajreal
  • 46,720
  • 11
  • 89
  • 119
0

one way is when you make the select query to omit this leading zero

http://www.foscode.com/remove-trailing-zeros-mysql-decimal-fields/

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
-2

You could just save whatever the user entered as a VARCHAR, so you'll store exactly what they entered. Then when you need it, you can load it from the DB, and parse it to the corresponding float value as needed.

mjuarez
  • 16,372
  • 11
  • 56
  • 73