Mysql – thesql data types: define as char but work as int

MySQL

I have to create a table including a column named tax. The data values of that column are such as 20k, 15k, and so on. So I used varchar(10) data type for that column. But when I have to do calculation or comparison , (eg) to find the tax more than 15k, I have to use that as an int (tax>15). Is the correct way? Or is there any better way?
I consider to change the column name as “tax(k)”. But I didn’t because I want to keep the table as the original table. In the original table, the column name is just tax and the data values are 10k, 15k and so on.

Best Answer

If there is no specific requirement for you to store the data in that manner then you SHOULD change the datatype of the column and make it to INT or Decimal as per your requirement.( This would be a suggestible approach as you will need to do a lot of calculations and there is no inbuilt function to calculate 'K' values)

However if there is not way out, you can use a replace function to replace the 'K' with a space or '000' and use INT/DECIMAL function to convert the same for your calculations. Syntax would be something as below 

SELECT * FROM TAX_TABLE WHERE CAST(REPLACE(TAX,'K','') AS DECIMAL) > 15

OR

SELECT * FROM TAX_TABLE WHERE CAST(REPLACE(TAX,'K','000') AS DECIMAL) > 15000
Related Question