Vfrdtyky

i've got stuck with something about datatypes in mysql

i have the following table:

id (int,primary key),

tipo (varchar(45)),

inteiro_pedaco (varchar(45)),

marca (varchar(45)),

a (decimal(11,1),

b (decimal(11,1),

c (int (11))

the A and B column should store data with the following format: 00.0, 00.1, etc.

But, A and B column should also be able to store data as if it was INT, not only decimal, however, if i try to put "10" for example, it automaticaly gets formated to "10.0"

The biggest problem is: those two columns (A and B) HAS to store 10 as value and other times 10.0, even if 10.0 is equal to 10, it has to be stored exactly the way it was sent, with or without the decimal. If the input was 10, it has to be stored as 10, if it was 10.0, it has to be 10.0

And i know that may sounds weird, but believe me, it will receive data as 10.0 sometimes, i wasn't using the 10.0 as example, it will REALLY receive data as 10.0 and 10

I tried reading about datatypes, but i could find none who fufil those requirements, except varchar, but i cant use varchar in this case, also, i cant make two separetely colunms to store the data, they gotta be in the same column

I was thinking about saving everything as decimal, and them formating it when doing a select, but in that case, i would have to make a new colunm just to store if it is decimal or not

So, there is any kinda of datatype who fits in the description above? If not, is formating the only way to do what i want?

Thanks for the help

You might want to consider having multiple columns. eg DEC_VAL, INT_VAL, STR_VAL

This will solve join issues and filter issues but is slightly harder to maintain.

Sounds like the problem is when you get the data as opposed to how it's stored, if the decimal point is throwing things off (like you want a certain appearance during output) maybe try a case statement for the select.

select case when cast(cast(A as int) as decimal) = A then cast(A as int) else A end

What we are doing here is testing if column A as an int is equal to column A as a decimal, and if they are the same we are casting it as an int. If they are not equal we leave column A as a decimal.

As the others noted this is kind of an odd problem, because most things will evaluate 1.0 = 1, but I'm guessing this is formatting for the output as opposed to a logical evaluation down the line. Anyway hope this helps :)

If trailing or leading variables are important, then you should store the vaue as a string. You really do not have much choice.

Basically, the value is not a number, it is some sort of code. In the world of numbers, 10.0 is equal to 10.

EDIT:

Based on your comment, you should have two columns that are both integers. One is for "the part before the decimal point" the second is for the range.

You are making a mistake by trying to put two things in one column.