Insert non-English decimal points in mysql

Question

How can you make mysql understand non-English decimal points in integer/float fields?

setLocale(LC_ALL, 'da_DK.ISO-8859-1');

eg:

0,25 will be inserted as 0
0.25 will be inserted as 0.25

Why would `0,25` insert as zero? SQL had a DECIMAL data type, so I'd expect the value to be stored as `0.25` — OMG Ponies, Jul 08 '11 at 16:15
I'm assuming the parser can't handle it and is breaking on comma. — Tremmors, Jul 08 '11 at 16:41

score 15 · Accepted Answer · answered Jul 08 '11 at 16:33

15

One of the reasons you can't do that is that the comma , is used for separating field-values in INSERT statements.

INSERT INTO a(b,c) VALUES (3,4,5) would be ambiguous.

Should it result into b=3.4 , c=5 or b=3 , c=4.5 ?

answered Jul 08 '11 at 16:33

ypercubeᵀᴹ

113,259
19
174
235

good answer. I'll never understand WHO designated the painful comma as a separator of things when there are at least [4 separators in ASCII](https://en.wikipedia.org/wiki/Delimiter#ASCII_delimited_text) – Ivan Ferrer Villa Oct 14 '16 at 12:05
@IvanFerrerVilla those are non printable characters, aimed to be used in files. SQL is a language that is to be readable. – ypercubeᵀᴹ Oct 14 '16 at 12:10
yes, but from the beginnings of computer ages they could have invented a printable/viewable separator char instead of using the ordinary comma. And add a key to the standard keyboard to write it. We use commas everywhere in our languaje! :) – Ivan Ferrer Villa Oct 14 '16 at 14:32

score 3 · Answer 2 · answered Jul 08 '11 at 16:20

3

Numbers aren't affected by locale settings (same in SQL Server too).

So you'd have to send as "0.25"

answered Jul 08 '11 at 16:20

gbn

422,506
82
585
676

score 0 · Answer 3 · answered Jul 08 '11 at 16:28

The easiest way I would think would be to use a wrapper for your mysql object that will set the locale to en_US.utf8 before your query and after the query puts it back to da_DK.ISO-8859-1.

It looks like this does the opposite of what you're looking for.

Vijay · Answer 4 · 2013-12-13T14:39:09.713

-4

You might want to try something like REPLACE('3,1415926', ',' , '.' )

edited Dec 13 '13 at 14:39

answered Dec 13 '13 at 14:29

Vijay

1
1

This would only work if you have _exactly_ `3,1415926` as a string somewhere and would have to perform this for _every_ occurrence you wish to change. Hardly useful in my opinion. – Werner Dec 13 '13 at 14:53
1

This is not a solutions of asked question. please read question again carefully – Umar Abbas Sep 18 '15 at 10:57

Insert non-English decimal points in mysql

4 Answers4

Linked

Related