MySQL -- insert a VARCHAR value from T1 into a DECIMAL field in T2

In Table_1, I have imported data from a spreadsheet. In one table column there are mixed values: 94.7, 45.0, 36, car, bus....etc. The datatype is VARCHAR(10)

I want to insert the values from this column from Table_1 into a DECIMAL(5,1) field in Table_2. I want the non-numeric values to register as 0.0. This is important.

I've tried several things, including CAST(), which works in my MySQL GUI, but gives a long series of warnings. Here is an example of the type of SQL I have tried.

DELETE FROM table_2;
INSERT INTO table_2 (NAME, decimal_column)
   (SELECT NAME
         , CAST(varchar_column AS DECIMAL(5,1))
      FROM table_1) ;

Example of MySQL warnings.

Warning Code : 1366 Incorrect decimal value: '' for column '' at row -1

Warning Code : 1292 Truncated incorrect DECIMAL value: 'house'

Also, PHP will have none of it. When I try to run the same query using PHP, I get this fatal error message:

Inc开发者_开发百科orrect decimal value: '' for column '' at row -1

Thanks for any suggestions

---

If you're clearing table_2 you can use TRUNCATE table_2; which may be faster.

One way to do this (though I don't know if it's the best; it may be a bit slow for large datasets) is to use a regular expression:

INSERT INTO table_2 (NAME, decimal_column)
(SELECT NAME
     , IF(varchar_column REGEXP '^[[:digit:].]+$', varchar_column, 0.0)
  FROM table_1) ;

This is assuming there's no whitespace in the decimal values of varchar_column. If there might be then you can use TRIM as follows:

IF(TRIM(varchar_column) REGEXP '^[[:digit:].]+$', TRIM(varchar_column), 0.0)

---

I've tried the following and it worked great for me:

INSERT INTO `table_2` (`decimal_column`) SELECT CAST(`varchar_column` AS DECIMAL(5,1)) FROM `table_1`;

The table I used had a blank value, a decimal value (as a VARCHAR) and the word "house" and everything converted as expected.

The warnings can be ignored. It's essentially MySQL telling you that it tried to convert data that it couldn't convert. The word "house" for example can't be converted into a decimal value so MySQL is warning you (and so is PHP.) The reason why you're not seeing the error in the MySQL GUI is because the error is suppressed because the query worked.

If you want to hide the errors, you can always add an @ symbol in front of the PHP call (i.e. @mysqli->query('...'), use ini_set('display_errors', false) to disable all of the errors, or error_reporting(E_NONE).