Solved

Prevent comma as decimal seperator

Posted on 2003-11-11
8
534 Views
Last Modified: 2008-02-26
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;
--------------------------------------------------------------------

0
Comment
Question by:nevahj
  • 3
  • 3
  • 2
8 Comments
 
LVL 17

Expert Comment

by:Squeebee
ID: 9731377
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
 
LVL 7

Accepted Solution

by:
jconde earned 50 total points
ID: 9732184
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
 
LVL 7

Expert Comment

by:jconde
ID: 9732213
>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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 17

Expert Comment

by:Squeebee
ID: 9732217
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
 
LVL 17

Expert Comment

by:Squeebee
ID: 9732294
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
 
LVL 7

Expert Comment

by:jconde
ID: 9732335
I should'v been able to deduce that ... 05:59AM PST seems pretty early for me :P
0
 
LVL 2

Author Comment

by:nevahj
ID: 9733353

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
 
LVL 2

Author Comment

by:nevahj
ID: 9733363

MUCH

"Thanks very MUCH for the quick resolution!"

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

- Charles
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question