MySQL comma to decimal point query

Question

I installed my C# system into a computer with Win XP, the number format for decimal separator was comma instead of decimal point, but, this interferes with the MySQL updates, because when I load the info into my C# system the format is "0,11" and the system considers the comma as part of the MySQL query. This is a problem, because every time I want to update a register I have to change manually the comma for a decimal point or otherwise it throws an exception.

Is there a way to change all the commas for decimal points in my database for an specific column? Even-though I already changed the regional configuration, the database keeps the comma in the previously registered items.

My table structure:

CREATE TABLE `inventario` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cla` varchar(100) NOT NULL,
  `des` varchar(500) NOT NULL,
  `lin` varchar(3) NOT NULL,
  `cal` varchar(20) NOT NULL,
  `uen` varchar(20) NOT NULL,
  `can` double NOT NULL,
  `fei` varchar(10) NOT NULL,
  `fec` varchar(10) NOT NULL,
  `obs` varchar(500) NOT NULL,
  `ppu` double NOT NULL,
  `pl1` double NOT NULL,
  `pl2` double NOT NULL,
  `pl3` double NOT NULL,
  `pl4` double NOT NULL,
  `prm` double NOT NULL,
  `pr1` varchar(50) NOT NULL,
  `pr2` varchar(50) NOT NULL,
  `mnm` double NOT NULL,
  `max` double NOT NULL,
  `dias` int(10) NOT NULL DEFAULT '1',
  `categoria` varchar(50) NOT NULL,
  PRIMARY KEY (`id`,`cla`),
  UNIQUE KEY `constr_cla` (`cla`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

The columns I need to update are the ones with double type. Thanks.

In time, these abbreviations will drive you mad. Also, anytime you sea x1, x2, etc. alarm bells shuold start ringing! — Strawberry, Jan 17 '14 at 16:07
possible duplicate of [Change decimal separator in MySQL](http://stackoverflow.com/questions/8669212/change-decimal-separator-in-mysql) — Strawberry, Jan 17 '14 at 16:08

Kuzgun · Accepted Answer · 2014-01-18T17:25:26.077

2

SELECT REPLACE(CAST(your_double AS CHAR), ',', '.')

This will select them replacing commas to dots and you can use this for an update query

An update version would be

    UPDATE your_table 
SET your_double=CAST(REPLACE(CAST(your_double AS CHAR), ',', '.') AS DOUBLE)

edited Jan 18 '14 at 17:25

answered Jan 17 '14 at 16:22

Kuzgun

4,649
4
34
48

This query only replaces in the select, it works, but I would prefer something that changes the data, is there an update version of this query? Thank you. – user3063952 Jan 17 '14 at 16:31

MySQL comma to decimal point query

1 Answers1