Page 1 of 1

Removing trailing zeros or decimals from a SQL decimal(7,3)

Posted: Thu Oct 01, 2009 2:09 am
by edawson003
I have field that I need to store in my database as a decimal(7,3) data point, but I would like to display on my front end as a one decimal place number or no decimal if there are no integers other than zero after the decimal.. Any thoughts?

I found this on teh net, but it didn't work:
SELECT replace(rtrim(replace(replace(rtrim(replace(field,'0',' '))
,' ','0'),'.',' ')),' ','.') from table
Any thoughts?

Re: converting decimal(7,3) data point to an int?

Posted: Thu Oct 01, 2009 2:27 am
by requinix
Use round: PHP will handle the conversions.

Re: converting decimal(7,3) data point to an int?

Posted: Thu Oct 01, 2009 2:11 pm
by edawson003
Actually, what I want is for the value that is stored in my SQL table to display as number with no decimal places for whole numbers and display the nearest tenth for decimal numbers.

For example:
150.000 should display as 150
169.450 should display as 169.5
20.340 should display as 20.4

Wouldn't applying the round() function just affect the digits. (i.e. 169.450 to 169.500)

Re: Removing trailing zeros or decimals from a SQL decimal(7,3)

Posted: Thu Oct 01, 2009 9:43 pm
by edawson003
On second thought:
replace(rtrim(replace(replace(rtrim(replace(field,'0',' ')),' ','0'),'.',' ')),' ','.') FROM table
does work. I just need to add a field alias, like so:
replace(rtrim(replace(replace(rtrim(replace(field,'0',' ')),' ','0'),'.',' ')),' ','.') AS field
so my mysql_fetch_array pick up the value by name.

Code: Select all

$lastentryquery = "SELECT replace(rtrim(replace(replace(rtrim(replace(field,'0',' ')),' ','0'),'.',' ')),' ','.') AS fieldFROM table WHERE enteredby = $enteredby ORDER BY entrydt DESC LIMIT 1"; 
$userresult = mysql_query($lastentryquery);
while($row = mysql_fetch_array($userresult)){
$fielddisplay = $row['field'];
I guess I answered my own post...gnarly!

Just thought I'd close the loop on how I resolved my issue so others to reference.

Re: Removing trailing zeros or decimals from a SQL decimal(7,3)

Posted: Thu Oct 01, 2009 10:04 pm
by jmaker

Code: Select all

 
echo round(150.000, 1);  // prints 150
echo round(169.450, 1);  // prints 169.5
echo round(20.340, 1);    // prints 20.3
 

Re: Removing trailing zeros or decimals from a SQL decimal(7,3)

Posted: Thu Oct 01, 2009 10:14 pm
by edawson003
Or... I could just use

Code: Select all

echo round(field, 1);


Thx, jmaker!

I'll just scoot back to my newbie corner. :oops: