MySQL | Error Code: 1292. Truncated incorrect DOUBLE value: '149.89'

From sourcetable "amazon_order_history_2022" and column "Betrag" (datatype: varchar) I want to calculate a new column, that classifies data from column "Betrag", wether it smaller/equal to 100 or bigger than 100. So far the code is working.

Then I want to create a new table, that contains the result of above calculation and that is where "Error Code: 1292. Truncated incorrect DOUBLE value: '149.89' " occurs.

DROP Table IF EXISTS SamyDB.synthese;
CREATE TABLE SamyDB.synthese AS
With temp1 as (
Select
Betrag
,replace(Betrag,'€ ','') as Betrag_clean
from amazon_order_history_2022
)
, temp2 as(
SELECT
Betrag
,Betrag_clean
,case 
    when cast(Betrag_clean AS DOUBLE) <= 100 then 'small'
    when cast(Betrag_clean AS DOUBLE) > 100 then 'big'
end as 'spent_temp'
FROM temp1
)
select *
from temp2

I was expecting to see the result of "select" calculation in the new table (see screenshot), but instead I receive "Error Code: 1292. Truncated incorrect DOUBLE value: '149.89' ", when running the code with the "create table" statement.

Can anybody please support me? Thank You very much in advance.

You have a comma ',' inside your value and it thusly cannot convert it to a proper DOUBLE value. Simply replace the , with a . as well and it should work:

DROP TABLE synthese;
CREATE TABLE synthese AS
With temp1 as (
Select
Betrag
,replace(replace(Betrag,'€ ',''), ',', '.') as Betrag_clean
from amazon_order_history_2022
)
, temp2 as(
SELECT
Betrag
,Betrag_clean
,case 
    when cast(Betrag_clean AS DOUBLE) <= 100 then 'small'
    when cast(Betrag_clean AS DOUBLE) > 100 then 'big'
end as 'spent_temp'
FROM temp1
)
select *
from temp2