MySQL and REGEXP to handle digits with different decimal mark systems when loading from csv

I need to import csv files to mysql DB using "LOAD DATA LOCAL INFILE". The problem is that the csv files are generated by some machines which are using European decimal mark (comma)  and other machines which are using American decimal mark (dot). My database is using American system, so I need to be able to read from the csv and populate my database correctly.

here are all the possible cases

-----------------            -----------------------------
CSV                           desired result in the DB
-----------------            -----------------------------
  [empty]            ==> null
- [dash]              ==> null
100,000.00        ==>   100000.00
-100,000.00       ==> -100000.00
100.000,00        ==> 100000.00
100,000,000.00 ==> 100000000.00
100.000.000,00 ==> 100000000.00

Select allOpen in new window

I figured out that I could use Replace:

REPLACE(REPLACE("100.000.000,00", '.', ''), ',', '.')   ==> 100000000.00
REPLACE(REPLACE("100.000.000,00", ',', ''), '.', '.')   ==> 100000000.00

But I need to know the decimal mark to determine which replace to use.

The solution seems to use IF with regular expression REGEXP to determine the number format and then use the correct REPLACE


my incomplete sql query looks like this:

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\TEMP\\data.csv'
INTO TABLE `test`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'
IGNORE 9 LINES
(@c1) 
set 
`budget`=IF(IFNULL(@c1), "")  = '-', null,REPLACE(REPLACE(@c1, '.', ''), ',', '.'));

Select allOpen in new window