How to remove trailing zeros from MySql decimal fields

My quickest tutorial to date, simple mysql query to remove trailing zeros from decimal fields.
Initially our data looks like this:

mysql> SELECT testid, designationid, test, measure,setpoint, tmin,tmax FROM test
*************************** 1. row ***************************
       testid: s5_28
designationid: Volume
         test: CH A Volume Test 40ml @ 400ml/hr
      measure: ml
     setpoint: 40.00
         tmin: 38.00
         tmax: 42.01
*************************** 2. row ***************************
       testid: s5_29
designationid: Volume
         test: Battery Test 20ml @ 40ml/hr
      measure: ml
     setpoint: 20.00
         tmin: 19.00
         tmax: 21.00
*************************** 3. row ***************************
       testid: s5_30
designationid: Volume
         test: CH B Volume Test 40ml @ 400ml/hr
      measure: ml
     setpoint: 40.00
         tmin: 38.00
         tmax: 42.00
*************************** 4. row ***************************
       testid: s5_4
designationid: Pressure
         test: Pneumatic Leak Test
      measure: mm/Hg
     setpoint: 3.00
         tmin: 0.00
         tmax: 0.01
*************************** 5. row ***************************
       testid: s5_5
designationid: Pressure
         test: Diastolic 200
      measure: mm/Hg
     setpoint: 200.00
         tmin: 197.00
         tmax: 0.01
*************************** 6. row ***************************
       testid: s5_6
designationid: Pressure
         test: Systolic 200
      measure: mm/Hg
     setpoint: 200.00
         tmin: 147.00
         tmax: 1.00
*************************** 7. row ***************************
       testid: s5_7
designationid: Pressure
         test: Systolic 150
      measure: mm/Hg
     setpoint: 150.00
         tmin: 147.00
         tmax: 1.05

And this is the query to remove the trailing zeros:

mysql> SELECT testid, designationid, test, measure, 
              (TRIM(TRAILING '.' FROM(CAST(TRIM(TRAILING '0' FROM setpoint)AS char)))) AS setpoint,
              (TRIM(TRAILING '.' FROM(CAST(TRIM(TRAILING '0' FROM tmin)AS char)))) AS tmin,
              (TRIM(TRAILING '.' FROM(CAST(TRIM(TRAILING '0' FROM tmax)AS char)))) AS tmax,              
             FROM
          tests

And the resultset:

*************************** 1. row ***************************
       testid: s5_28
designationid: Volume
         test: CH A Volume Test 40ml @ 400ml/hr
      measure: ml
     setpoint: 40
         tmin: 38
         tmax: 42.01
*************************** 2. row ***************************
       testid: s5_29
designationid: Volume
         test: Battery Test 20ml @ 40ml/hr
      measure: ml
     setpoint: 20
         tmin: 19
         tmax: 21
*************************** 3. row ***************************
       testid: s5_30
designationid: Volume
         test: CH B Volume Test 40ml @ 400ml/hr
      measure: ml
     setpoint: 40
         tmin: 38
         tmax: 42
*************************** 4. row ***************************
       testid: s5_4
designationid: Pressure
         test: Pneumatic Leak Test
      measure: mm/Hg
     setpoint: 3
         tmin: 0
         tmax: 0.01
*************************** 5. row ***************************
       testid: s5_5
designationid: Pressure
         test: Diastolic 200
      measure: mm/Hg
     setpoint: 200
         tmin: 197
         tmax: 0.01
*************************** 6. row ***************************
       testid: s5_6
designationid: Pressure
         test: Systolic 200
      measure: mm/Hg
     setpoint: 200
         tmin: 147
         tmax: 1
*************************** 7. row ***************************
       testid: s5_7
designationid: Pressure
         test: Systolic 150
      measure: mm/Hg
     setpoint: 150
         tmin: 147
         tmax: 1.05

This is it, enjoy, and as always thank you for stopping by and please share with others, after all, code should be free.foscode.com | because code should be free

2 thoughts on “How to remove trailing zeros from MySql decimal fields

  1. @panega You are welcome, thanks for stopping by my site, I’m glad I was able to help.

Leave a Reply

Your email address will not be published. Required fields are marked *

*


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>