Solved

Prevent comma as decimal seperator

Posted on 2003-11-11
8
530 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now