Optional ".00" in Decimal Field

[bdichiara]

I need the sort features of a DECIMAL Field, but the functionality of a VARCHAR field.

I want a field in my table to hold values such as:

50
100
5
15.25
25.80
90.11
8
17
30.65

I don't want the database to automatically add the ".00" to the end of the values that don't have it. How can I accomplish this?

_______________
_brian.

 

[sleipnir214]

If this is decimal data, why would it matter whether there are extra decimals and zeros added? They are mathematically identical.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[bdichiara]

becuase in my previous post i mentioned that if the value has a decimal in it, then it's money and I need to format it accordingly, but if it doesn't have it then it's just a number, like # of sales or something.

_______________
_brian.

 

[sleipnir214]

I strongly recommend that you keep different types of values in different columns.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[bdichiara]

I will take your recommendation into consideration, but are you saying there is not a way to do this at table level?

_______________
_brian.

 

[guelphdad]

I'll guess you are using a lookup table where you have one column containing what the value is, money, quantity, part number etc. and a second column holding the value for each of those types of items.

If you are you most certainly should reconsider your database design.
this article and this one give pretty good explanations as to why you want to avoid that type of set-up.

 

[sleipnir214]

A DECIMAL(10,2) column doesn't have to have the decimal or zeros, and I don't believe that MySQL will add them if they are not there.

The second article guelphdad linked ends with "Maybe there are good reasons for the data modeling principle that a well-designed table is a set of things of the same kind instead of a pile of unrelated items." That same idea can be applied to the individual columns of a table.

You're trying to store float currency data and integer count data in the same column. You can't sort effectively, you can't select well. And what happens when you need to store both types of data?

Use separate columns for separate types of data.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[bdichiara]

Well, that's what I ended up doing.

_______________
_brian.