Decimal points

------=_NextPart_000_0004_01CB26EF.224493F0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

mysql> select 1+1.0;

+-------+

| 1+1.0 |

+-------+

| 2.0 |

+-------+

1 row in set (0.00 sec)



mysql> select 1+1.1;

+-------+

| 1+1.1 |

+-------+

| 2.1 |

+-------+

1 row in set (0.00 sec)





Is there a way to tell MySql to only return '2' in the first select as
opposed to '2.0'? The second select is correct and should remain as such.



Basically I have two columns, one with an integer and another with a
decimal. And I'm adding the two, but for those where the decimal has a .0,
I just want the result to not have the .0 and for those that do have
anything other than .0, to display it accordingly.



mysql> select version();

+------------+

| version() |

+------------+

| 5.1.47-log |

+------------+


------=_NextPart_000_0004_01CB26EF.224493F0--

Re: Decimal points

On 19/07/2010 10:04, Ashley M. Kirchner wrote:
>
> Is there a way to tell MySql to only return '2' in the first select as
> opposed to '2.0'? The second select is correct and should remain as such.

Not easily, no.

> Basically I have two columns, one with an integer and another with a
> decimal. And I'm adding the two, but for those where the decimal has a .0,
> I just want the result to not have the .0 and for those that do have
> anything other than .0, to display it accordingly.

This is the sort of thing that is far better handled in the application
layer, rather than the database layer. PHP, for example, even has a
built-in function which will do this:

setype($value,"float");


for example:

$val = "2.1";
setype($val,"float");
echo $val;
?>

=> 2.1

$val = "2.0";
setype($val,"float");
echo $val;
?>

=> 2

http://www.php.net/manual/en/function.settype.php

Even if other languages don't have built-in functions to do this, it's a
trivial piece of code to recreate it.

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Decimal points

SELECT IF((col1+col2) = FLOOR(col1+col2),FLOOR(col1+col2), col1+col2)

EXAMPLE>>>

SELECT IF((1+1.1) = FLOOR(1+1.1),FLOOR(1+1.1),1+1.1) Results 2.1

SELECT IF((1+1.0) = FLOOR(1+1.0),FLOOR(1+1.0),1+1.0) Results 2

Regards,

Samrat Kar

-----Original Message-----
From: Ashley M. Kirchner [mailto:ashley@pcraft.com]
Sent: Monday, July 19, 2010 2:35 PM
To: mysql@lists.mysql.com
Subject: Decimal points

mysql> select 1+1.0;

+-------+

| 1+1.0 |

+-------+

| 2.0 |

+-------+

1 row in set (0.00 sec)



mysql> select 1+1.1;

+-------+

| 1+1.1 |

+-------+

| 2.1 |

+-------+

1 row in set (0.00 sec)





Is there a way to tell MySql to only return '2' in the first select as
opposed to '2.0'? The second select is correct and should remain as such.



Basically I have two columns, one with an integer and another with a
decimal. And I'm adding the two, but for those where the decimal has a .0,
I just want the result to not have the .0 and for those that do have
anything other than .0, to display it accordingly.



mysql> select version();

+------------+

| version() |

+------------+

| 5.1.47-log |

+------------+



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Decimal points

Hi,

Let try '' to treat numeric as character
For example:
select 1+'1.0';
==> 2
select 1+'1.1';
==> 2.1


----- Original Message -----
From: "Mark Goodge"
To: mysql@lists.mysql.com
Sent: Monday, July 19, 2010 4:31:51 PM
Subject: Re: Decimal points

On 19/07/2010 10:04, Ashley M. Kirchner wrote:
>
> Is there a way to tell MySql to only return '2' in the first select as
> opposed to '2.0'? The second select is correct and should remain as such.

Not easily, no.

> Basically I have two columns, one with an integer and another with a
> decimal. And I'm adding the two, but for those where the decimal has a .0,
> I just want the result to not have the .0 and for those that do have
> anything other than .0, to display it accordingly.

This is the sort of thing that is far better handled in the application
layer, rather than the database layer. PHP, for example, even has a
built-in function which will do this:

setype($value,"float");


for example:

$val = "2.1";
setype($val,"float");
echo $val;
?>

=> 2.1

$val = "2.0";
setype($val,"float");
echo $val;
?>

=> 2

http://www.php.net/manual/en/function.settype.php

Even if other languages don't have built-in functions to do this, it's a
trivial piece of code to recreate it.

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=cuong.manh@vienthongso.co m


--
Best Regards,
Cuongmc.

--
Nguyen Manh Cuong
Phong Ky Thuat - Cong ty Vien Thong So - VTC
Dien thoai: 0912051542
Gmail : philipscuong@gmail.com
YahooMail : philipscuong@yahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Decimal points

Mark Goodge wrote:
>
> This is the sort of thing that is far better handled in the
> application layer, rather than the database layer. PHP, for example,
> even has a built-in function which will do this:
>
> setype($value,"float");
>

I agree about using the application layer, but I like to use type
casting instead.

$value = (float) $value;


http://www.php.net/manual/en/language.types.type-juggling.ph p#language.types.typecasting

I try to avoid asking why but in this case I have to. I can't imagine
wanting to have a list of numbers displayed and not have them all
aligned right with the sane number of digits after the decimal point.
So why would you even want to do this is?

Chris W

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Re: Decimal points

On 7/20/2010 10:07 AM, Chris W wrote:
> I try to avoid asking why but in this case I have to. I can't imagine
> wanting to have a list of numbers displayed and not have them all
> aligned right with the sane number of digits after the decimal
> point. So why would you even want to do this is?

Wish I can give you an answer, but it's a requirement from the
client. Their application needs it displayed that way. '101' and
'101.0' appear to have completely different meanings. Especially since
more often than not, it's followed by a letter. I don't know, I'm just
the programmer here.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Re: Decimal points

Just out of curiosity, why not do it in the application layer?

Or maybe, you can try:
SELECT BINARY 1+1.2;
= 2.2
SELECT BINARY 1+1.0;
= 2
SELECT CAST(1+1.2 AS UNSIGNED);
= 2
SELECT CAST(1+1.6 AS UNSIGNED);
= 3 (so I guess rounding happens here)

And then I guess ultimately, you could also use FLOOR() CEIL() or ROUND()
SELECT ROUND(1+1.6, 0);
= 3
SELECT FLOOR(1+1.6);
= 2
SELECT CEIL(1+1.6);
= 3



Steven Staples


> -----Original Message-----
> From: Ashley M. Kirchner [mailto:ashley@pcraft.com]
> Sent: July 20, 2010 1:52 PM
> To: mysql@lists.mysql.com
> Subject: Re: [MySQL] Re: Decimal points
>
> On 7/20/2010 10:07 AM, Chris W wrote:
> > I try to avoid asking why but in this case I have to. I can't imagine
> > wanting to have a list of numbers displayed and not have them all
> > aligned right with the sane number of digits after the decimal
> > point. So why would you even want to do this is?
>
> Wish I can give you an answer, but it's a requirement from the
> client. Their application needs it displayed that way. '101' and
> '101.0' appear to have completely different meanings. Especially since
> more often than not, it's followed by a letter. I don't know, I'm just
> the programmer here.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=sstaples@mnsi.net
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.839 / Virus Database: 271.1.1/3009 - Release Date: 07/20/10
> 02:36:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org