Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Prevent comma as decimal seperator

Posted on 2003-11-11
8
533 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

837 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