How to remove, insert, or change a single character in a field in Mysql?

Question

I have the following table in Mysql:

Id   | comida     | preco
1    | batata     | 325
2    | carne      | 3.10
3    | speculoos  | 3.00

I wish I could change the points according to the needs below:

If the food = potato, insert a dot after the first character.

Case food = meat, delete the dot after the first character and insert it after the second.

And the last case food = speculoos, remove the dot after the first character.

How should I ride these darlings?

Answer 1

It is already complicated to treat value with decimals, even more converting to STRING and change character.

The ideal would be to use calculations, define formulas, and not a gambiarra.

Therefore, it is better to multiply and/or divide.

Example:

SELECT
(CASE WHEN comida = 'batata' THEN preco * 0.01 ELSE
(CASE WHEN comida = 'carne' THEN preco * 10 ELSE
(CASE WHEN comida = 'speculoos' THEN preco * 100 ELSE preco 
END) END) END) PRECO
FROM tabela

The query will work as follows: will try the 3 conditions, and if no answer, returns the preco normal.

---

Edit:

Yes, basically this, I understood the above logic for number, but what if in case they were words, how would I do it? - Shinchila_matadora

SELECT
(CASE WHEN comida = 'batata' THEN CAST(preco as DECIMAL(10,2)) * 0.01 ELSE
(CASE WHEN comida = 'carne' THEN CAST(preco as DECIMAL(10,2)) * 10 ELSE
(CASE WHEN comida = 'speculoos' THEN CAST(preco as DECIMAL(10,2)) * 100 ELSE CAST(preco as DECIMAL(10,2)) 
END) END) END) PRECO
FROM tabela