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

Question

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

Navigation

#1 by (1 votes)

0

I have the following table in MySql:

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

I wanted to be able to change the points according to the needs below:

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

If food = meat , delete the point after the first character and insert it after the second character.

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

How do I mount these queries ?

mysql query substring

asked by anonymous 17.08.2018 / 17:56

1 answer

Error: The object reference was not defined as an instance of a C # Insert text of a variable (resulting from an if) within a p after a button click

score 1 · Accepted Answer

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 , not a gambiarra.

Before this, then better 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 none of them meet, returns the preco normal

.

Edit:

Yes, basically this, I understood the logic above for number, but what if in case they were words, how would I do that? - 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