Insert conversion using MySQL

Question

I'm trying to insert a value into a table in MySQL but can't get it to work. I'm using the following query:

INSERT into articulo values (32,'Sala',CAST(',000.45999' AS DECIMAL(10,5)),40.2399,200.2399,3,'kid 3');

MySQL displays the following error:

1 row(s) affected, 1 warning(s): 1292 Truncated incorrect DECIMAL value: ',000.45999'

The following content is displayed in the table:

Of course, I created the table "articulo" before:

CREATE Table articulo
(
 id_art int NOT NULL,
 nom_art varchar (25) DEFAULT 'XXXXXXXXXXXXX',
 prec_art decimal (10,5) DEFAULT 0.000,
 peso_art decimal (10,5),
 existencia float,
 color_art int, CONSTRAINT chk_color1 CHECK (color_art between 0 and 20),
 um_art varchar (10) DEFAULT 'DEF_PZA',
 primary key (id_art)
);

I have seen many examples of coercion, but they all use the coercion function under select statement.

Any idea what I can do to perform what I want?

I want to store $10,000.45999 as a decimal value into the table. This will be 10000.45999

thank you for your support!

Answer 1

You cannot use commas or dollar signs in values ​​in this query.

You can rewrite the query as:

INSERT into articulo values (32,'Sala',CAST('10000.45999' AS DECIMAL(10,5)),40.2399,200.2399,3,'kid 3');

However, if your column is explicitly defined as DECIMAL(10,5), there is no need to convert the values ​​to decimals.

Simply write:

INSERT into articulo values (32,'Sala',10000.45999,40.2399,200.2399,3,'kid 3');

Answer 2

You can insert values ​​by fixed numbers. For your case this should work:

INSERT into articulo 
    SELECT 32, 'Sala',
           CAST(REPLACE(REPLACE(',000.45999', ',', ''), '$', '') AS DECIMAL(10,5)),
           40.2399, 200.2399, 3, 'kid 3';

Strictly speaking, cast() is not required, but I like to avoid implicit conversions - these can cause hard-to-detect problems.

Please note: It is best to include the column list in the insert statement.