24

Is it possible to change the decimal comma from "." (dot) to other character (comma) in MySQL output? I don't want to use functions like FORMAT, I just want to use all the queries I normaly use without any modification. I'm looking for some setting (of some variable, locale etc.). I tried to search the manual but without success.

Tomas
  • 57,621
  • 49
  • 238
  • 373
  • 1
    I don't really understand this - what do you mean by "MySQL output"? Displaying a query result via the mysql command line client? Numeric values don't have a decimal separator defined by MySQL, rather they are defined by the language and framework used to output them for human use. – Matt H Dec 29 '11 at 14:45
  • 1
    @MattH, I mean any client output (console, PHP, whatever). The problem in PHP is that you always get string, even if the database field is of type `double`. So there's no obvious generic solution on the PHP side, so I was looking in MySQL. – Tomas Dec 29 '11 at 14:56

2 Answers2

36

Tip for CSV exports: SELECT REPLACE(CAST(prijs_incl AS CHAR), '.', ',') will give you input that can be used as numeric fields in european excelsheets.

Tomas
  • 57,621
  • 49
  • 238
  • 373
Ome Ko
  • 361
  • 3
  • 2
  • 18
    Useful, althou you could directly use `SELECT REPLACE(prijs_incl, '.', ',')` as string conversion is automatic, see [mysql doc](http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast) – vicenteherrera Jun 26 '12 at 11:56
  • 1
    This will not work for numbers less than 1000. For example: 1.000,00 is going to be 1,000,00 . So it will not work properly – Guilherme Oliveira Oct 25 '19 at 17:34
24

No, you can't. That's the SQL standard and MySQL complies with it (in that point at least).

The problem is not really with output (as you mention, there are various FORMAT functions in most DBMSs) but with INSERT. If you could use comma , for example as decimal point (that's common in other locales) which is aslo used as values separator, Inserts would become ambiguous. See my answer in the question: insert-non-english-decimal-points-in-mysql

Community
  • 1
  • 1
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • ypercube, thanks. What is the SQL standard? The SQL query syntax, or the output format, or both? I only need the output, so that if I do `select 1/3` it outputs `0,33333`. – Tomas Dec 29 '11 at 20:29
  • I'm not sure if the output format is specified in the SQL standards or that is left out and can be different for different connection methods/frameworks. But I know of no way of doing that in MySQL. – ypercubeᵀᴹ Dec 29 '11 at 22:19