10

I would like to replace any 'dot' character in my query string, on SELECTing fields from database.

I'll need to modify lots of queries, I'm willing there's a function that will work to all columns on SELECT. I mean something like this SELECT DOT_TO_COMMA(*) FROM...

Right now what I have:

SELECT price, lastprice FROM products

OUTPUT: 22.10, 5.24

EXPECTATION: 22,10, 5,25

user1876234
  • 857
  • 2
  • 14
  • 28
  • use the [REPLACE](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace) function? – scragar Jun 05 '14 at 09:20
  • You do realize that your "expectation" as for numbers instead of 2 : 22, 10 5 and 25 ... – mb14 Jun 05 '14 at 09:23
  • what version of mysql? – arilia Jun 05 '14 at 09:27
  • 1
    This doesn't directly address your issue but may be worth a look: [8669212/change-decimal-separator-in-mysql](http://stackoverflow.com/questions/8669212/change-decimal-separator-in-mysql). – Ryan Vincent Jun 05 '14 at 09:29
  • @mb14 thanks, for precaution. All columns will have 200.30 format so, that shouldn't be a problem. – user1876234 Jun 05 '14 at 09:29

3 Answers3

35
SELECT REPLACE(price, '.', ',') AS price
FROM products;
  • read more about it here

You have to wrap each column you need to replace with the function. Using replace(*) is not possible.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
0

please try this...

this is working

SELECT REPLACE(price,'.',',') AS price, REPLACE(lastprice,'.',',') AS lastprice FROM products
Ajs Rock
  • 89
  • 4
0

In my case replace doesn't work well with negative numbers.

I use SELECT FORMAT (price,0). Second parameter is de decimal numbers

checkout http://www.mysqltutorial.org/mysql-format-function/

rickslayer
  • 29
  • 1