Datatype Conversion Query - Varchar to Float

Question

When performing a query, I need a column of a given table to convert your Datatype for FLOAT.

COLUMN CONFIGURATION

Name: 16_remuneracao_sem_13
Datatype: VARCHAR(15)

PERFORMING STANDARD CONSULTATION

SELECT `16_remuneracao_sem_13` as 'SALÁRIO' FROM t_trabalhador;

RETURN:

EXPLANATION The data we receive as a result of the consultation represent monetary values. Where the last two characters correspond to two fractional squares. Thus the value 000000000119760 at the time of conversion to FLOAT needs to be added to the comma, as a result we obtained 0000000001197.60.

ADDING A COMMA TO THE VALUE To solve the comma problem I used the custom query below.

SELECT 
CONCAT(SUBSTR(`t_trabalhador`.`16_remuneracao_sem_13`, 1, 13), ".",             
SUBSTR(`t_trabalhador`.`16_remuneracao_sem_13`, 14, 2)) as 'SALÁRIO' 
FROM t_trabalhador;

RETURN

SALÁRIO
0000000006266.00
0000000009310.00
0000000001197.60
0000000001080.00
0000000006266.00
0000000009310.00
0000000001197.60
0000000001080.00
0000000006266.00

HOW TO RETURN THE STRING AS A FLOAT I need Mysql to return the value of the string as float bringing me as a result a return as follows below.

INTENDED RESULT Remembering that I need SQL to actually return as Float, I can’t replace zeros for whitespace.

SELECT 
0 + CONCAT(SUBSTR(`t_trabalhador`.`16_remuneracao_sem_13`, 1, 13), ".",     
SUBSTR(`t_trabalhador`.`16_remuneracao_sem_13`, 14, 2)) as 'SALÁRIO' 
FROM t_trabalhador;

RETURN

SALÁRIO
6266
9310
1197.6
1080
6266
9310
1197.6
1080
6266

The solution here differs in the fact that you can define where the comma will be in the string. If this proposal was not useful to take a look at this other post that has the same purpose but with a simpler need.

Answer 1

Test if this solves your problem, using the Mysql TRIM function, you have HERE the documentation.

Query

SELECT TRIM(TRAILING '0' FROM `16_remuneracao_sem_13`)+0
FROM `t_trabalhador`;