Prevent comma as decimal seperator

The following code demonstrates a problem I am having where only numbers before (to the left of the) comma are only stored -- everything to the right is ignored.

First, is this behavior normal or a bug in MySQL? Is this a locale setting that I can change?

--------------------------------------------------------------------
create database test_numbers;
use test_numbers;

CREATE TABLE numbers1 (
  row_id int(4) unsigned not null AUTO_INCREMENT PRIMARY KEY,
  row_value float(15,5)      
);

insert into numbers1 (row_id, row_value) values(null, .01);
insert into numbers1 (row_id, row_value) values(null, 1.23);
insert into numbers1 (row_id, row_value) values(null, 12.34);
insert into numbers1 (row_id, row_value) values(null, 123.45);
insert into numbers1 (row_id, row_value) values(null, 1234.56);
insert into numbers1 (row_id, row_value) values(null, 12345.67);

CREATE TABLE numbers2
SELECT
  row_id as new_row_id,
  format(row_value,2) as new_row_value
from numbers1;

CREATE TABLE numbers3
SELECT
  new_row_id as final_row_id,
  new_row_value as final_row_value,
  0.00 as fun
from numbers2;

UPDATE numbers3, numbers2
SET numbers3.fun = numbers2.new_row_value
WHERE numbers2.new_row_id = numbers3.final_row_id;

select * from numbers3;
--------------------------------------------------------------------

LVL 2
nevahjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SqueebeeCommented:
Just a question, but why are you storing decimal values as an INT column? Everything after the decimal will be lost. Try changing INT UNSIGNED to FLOAT.
0
jcondeCommented:
Hi!

Here's the problem:

format(row_value,2) as new_row_value

From the manual:

FORMAT(X,D)
Formats the number X to a format like '#,###,###.##', rounded to D decimals, and RETURNS THE RESULT AS A STRING. If D is 0, the result will have no decimal point or fractional part

So basically, I bet if you do a desc numbers2, new_row_value will not be a float anymore ... it will be a char(X) field.

With the above in mind, its not that mysql treats , as a decimal separator .... the problem is because of the way you're creating the tables you're basically forcing an insert and since , is not a valid character for precision fields, it truncates from the , onwards.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jcondeCommented:
>Try changing INT UNSIGNED to FLOAT

I beleive Squeebee is talking  numbers1.row_id (the primary key) .... I don't understand where that comment comes from, but if my assumption is correct, you SHOULD NEVER use precision fields as primary keys .... precision fields are not fixed, they're calculated.  In other words, if you use a float as a primary key, you may take for granted you'll have serious problems using the primary key as part of a where clause (or having clause).
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

SqueebeeCommented:
Man, I need to wake up before I post.

jconde has it, you are creating tables using a format function. All formatting should be done in the final select, not the selects leading up to it.
0
SqueebeeCommented:
No jconde, I just posted first thing this morning before really getting my head on straight it would appear. You are correct, never use a precision (or date) field as part of a primary key.
0
jcondeCommented:
I should'v been able to deduce that ... 05:59AM PST seems pretty early for me :P
0
nevahjAuthor Commented:

You guys were right -- it was the format() function mucking everything up. I did notice it was making the numbers appear as text but, couldn't figure out how it was doing it.

AFA the confusion surrounding the INT field -- that was my primary key -- not a float and the only field I defined in that manner.

Thanks very the quick resolution!
Charles
 
0
nevahjAuthor Commented:

MUCH

"Thanks very MUCH for the quick resolution!"

Which way to the "how to talk" forum?!?

- Charles
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.