Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Prevent comma as decimal seperator

Posted on 2003-11-11
8
Medium Priority
?
537 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
[X]
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
  • 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 200 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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