CODEWITHSUNDEEP
mysqlreplacenumeric
user2061666
user2061666

Reputation:

MySQL: Check for numeric values and change "," to "."

I got the following problem:

I am regularly uploading values into a MySQL database and have to check for numeric values (incl. float). Unfortunately some of these numeric values have been entered as for example 1,23 instead of 1.23.

I now want MySQL to do a table update and change these values to true numeric values (1.23) for all values which are numeric after changing a possible "," to "."

Example:

Left, Right should stay as Left, Right

120 should stay as 120

2.5 should stay as 2.5

0,125 should become 0.125

Anyone got any idea? Thanks a lot!

Upvotes: 0

Views: 426

Answers (2)

funtime
funtime

Reputation: 627

Check if your column is form of numeric or not.. You can use MYSQL RLIKE operator which matches regular expressions for this kind of situations

UPDATE TABLE_NAME SET COL_NAME=replace(COL_NAME,  ','  , '.'  ) WHERE COL_NAME RLIKE '([0-9])+,([0-9])+'

Upvotes: 1

Rohit Gupta
Rohit Gupta

Reputation: 541

UPDATE your_table SET your_field = REPLACE(your_field, ',', '.') WHERE your_field LIKE '%,%'

Upvotes: 0

Related Questions