Remove trailing zeros from decimal in SQL Server

Question

I have a column DECIMAL 9 6  i e  it supports values like 999 123456   But when I insert data like 123 4567 it becomes 123 456700  How to remove those zeros

User · Answer

I had a similar issue  but was also required to remove the decimal point where no decimal was present  here was my solution which splits the decimal into its components  and bases the number of characters it takes from the decimal point string on the length of the fraction component  without using CASE   To make matters even more interesting  my number was stored as a float without its decimals   DECLARE  MyNum FLOAT SET  MyNum   700000 SELECT CAST PARSENAME CONVERT NUMERIC 15 2   MyNum 10000  2  AS VARCHAR 10      SUBSTRING     1 LEN REPLACE RTRIM REPLACE CAST PARSENAME CONVERT NUMERIC 15 2   MyNum 10000  1  AS VARCHAR 2    0             0        REPLACE RTRIM REPLACE CAST PARSENAME CONVERT NUMERIC 15 2   MyNum 10000  1  AS VARCHAR 2    0             0      The result is painful  I know  but I got there  with much help from the answers above

User · Answer

I understand this is an old post but would like to provide SQL that i came up with  DECLARE  value DECIMAL 23 3  set  value   1 2000 select  value original val       SUBSTRING   CAST   value as VARCHAR 100                     0                  PATINDEX       CAST  value as VARCHAR 100                          CASE WHEN ROUND                           REVERSE  SUBSTRING  CAST  value as VARCHAR 100                                            PATINDEX       CAST  value as VARCHAR 100    1                                          LEN CAST  value as VARCHAR 100                                                                                                     1   gt  0 THEN                                  REVERSE ROUND REVERSE SUBSTRING  CAST  value as VARCHAR 100                                                    PATINDEX       CAST  value as VARCHAR 100    1                                                  LEN CAST  value as VARCHAR 100                                                                        1           ELSE    END  AS modified val

User · Answer

try this   select CAST 123 456700 as float  cast cast 123 4567 as DECIMAL 9 6   as float

User · Answer

I know this thread is very old but for those not using SQL Server 2012 or above or cannot use the FORMAT function for any reason then the following works   Also  a lot of the solutions did not work if the number was less than 1  e g  0 01230000    Please note that the following does not work with negative numbers   DECLARE  num decimal 28 14    10 012345000 SELECT PARSENAME  num 2    REPLACE RTRIM LTRIM REPLACE  num-PARSENAME  num 2   0              0     set  num   0 0123450000 SELECT PARSENAME  num 2    REPLACE RTRIM LTRIM REPLACE  num-PARSENAME  num 2   0              0      Returns 10 012345 and 0 012345 respectively

User · Answer

The best way is NOT converting to FLOAT or MONEY before converting because of chance of loss of precision  So the secure ways can be something like this     CREATE FUNCTION  dbo   fn ConvertToString         value sql variant   RETURNS varchar max  AS BEGIN     declare  x varchar max      set  x  reverse replace ltrim reverse replace convert varchar max     value   0             0        --remove  unneeded  dot  if any     set  x   Replace RTRIM Replace  x                         return  x END   where  value can be any decimal x y

User · Answer

Try this     SELECT REPLACE TRIM REPLACE 20 5500   0                0     Gives 20 55

User · Answer

How about this   Assuming data coming into your function as  thisData   BEGIN   DECLARE  thisText VARCHAR 255    SET  thisText   REPLACE RTRIM REPLACE  thisData   0                0     IF SUBSTRING  thisText  LEN  thisText   1            RETURN STUFF  thisText  LEN  thisText   1        RETURN  thisText END

User · Answer

Out of all the above answers  I found only two that actually worked  do not convert to float  proof here that 3 175 does not work if you do that  SELECT  round cast 3 175 as float   2  as roundingcheck      CASE WHEN     round cast 3 175 as float   2    3 18 THEN  PASSED  ELSE  FAILED  END     as roundecheck           CAST round TRY CONVERT DECIMAL 28 2   cast 3 175 as     float    2  as nvarchar max   as roundconvert        round CAST 3 175 as DECIMAL 18 10    4  as check1    cast CAST round CAST 3 175 as DECIMAL 18 10    4  as decimal 18 2   as nvarchar max    as     roundconvert2    cast CAST CAST 3 175 as DECIMAL 18 10   as decimal 18 2   as nvarchar max    as roundconvert3  cast CAST CAST 3 149 as DECIMAL 18 10   as decimal 18 1   as nvarchar max    as roundconvert4    cast FORMAT round CAST 3 175 as     DECIMAL 18 10    2    0          as nvarchar max    as roundconvert5  Result  3 17    FAILED  3 17    3 1750000000    3 18    3 18    3 1    3 18  Working answers   If you want to round to 2 decimal places at the same time  use this    cast CAST CAST 3 175 as DECIMAL 18 10   as decimal 18 2   as nvarchar max    as roundconvert3    If you don t know how many decimals there are going to be you can use this and use it with round if you need   cast FORMAT round CAST 3 175 as DECIMAL 18 10    2    0          as nvarchar max

User · Answer

it is possible to remove leading and trailing zeros in TSQL   Convert it to string using STR TSQL function if not string  Then  Remove both leading  amp  trailing zeros  SELECT REPLACE RTRIM LTRIM REPLACE AccNo  0              0   AccNo FROM  BankAccount  More info on forum

User · Answer

case when left replace ltrim rtrim replace str XXX  38  10    0                  0    1        then  0   else    end    replace ltrim rtrim replace str XXX  38  10    0                  0      case when right replace ltrim rtrim replace str XXX  38  10    0                  0    1        then  0   else    end

User · Answer

You can use the FORMAT   function  SqlAzure and Sql Server 2012     SELECT FORMAT CAST 15 12     AS DECIMAL 9 6     g18    --  15 12  SELECT FORMAT CAST 0 0001575 AS DECIMAL 9 6     g10    --  0 000158  SELECT FORMAT CAST 2 0       AS DECIMAL 9 6     g15    --  2    Be careful when using with FLOAT  or REAL   don t use g17 or larger  or g8 or larger with REAL   because the limited precision of the machine representation causes unwanted effects   SELECT FORMAT CAST 15 12 AS FLOAT    g17           --  15 119999999999999  SELECT FORMAT CAST 0 9 AS REAL    g8               --  0 89999998  SELECT FORMAT CAST 0 9 AS REAL    g7               --  0 9    Furthermore  note that  according to the documentation       FORMAT relies on the presence of the  NET Framework Common Language   Runtime  CLR   This function will not be remoted since it depends on   the presence of the CLR  Remoting a function that requires the CLR   would cause an error on the remote server    Works in SqlAzure  too

User · Answer

I needed to remove trailing zeros on my decimals so I could output a string of a certain length with only leading zeros   e g  I needed to output 14 characters so that 142 023400 would become 000000142 0234    I used parsename  reverse and cast as int to remove the trailing zeros   SELECT     PARSENAME 2 5500 2                  REVERSE CAST REVERSE PARSENAME 2 5500 1   as int      To then get my leading zeros I could replicate the correct number of zeros based on the length of the above and concatenate this to the front of the above   I hope this helps somebody

User · Answer

The easiest way is to CAST the value as FLOAT and then to a string data type   CAST CAST 123 456000 AS FLOAT  AS VARCHAR 100

User · Answer

SELECT CONVERT DOUBLE PRECISION   ColumnName

User · Answer

Try this   select isnull cast floor replace rtrim ltrim  999 999 0000             as int  0

User · Answer

Cast 20 5500 as Decimal 6 2     should do it

User · Answer

SELECT REVERSE ROUND REVERSE 2 5500  1    prints   2 55

User · Answer

A DECIMAL 9 6  column will convert to float without loss of precision  so CAST     AS float  will do the trick      HLGEM  saying that float is a poor choice for storing numbers and  Never use float  is not correct - you just have to know your numbers  e g  temperature measurements would go nicely as floats    abatishchev and  japongskie  prefixes in front of SQL stored procs and functions are still a good idea  if not required  the links you mentioned only instructs not to use the  sp   prefix for stored procedures which you shouldn t use  other prefixes are fine e g   usp   or  spBob    Reference   All integers with 6 or fewer significant decimal digits can be converted to an IEEE 754 floating-point value without loss of precision   https   en wikipedia org wiki Single-precision floating-point format

User · Answer

I was reluctant to cast to float because of the potential for more digits to be in my decimal than float can represent  FORMAT when used with a standard  net format string  g8  returned the scientific notation in cases of very small decimals  eg 1e-08  which was also unsuitable  Using a custom format string  https   docs microsoft com en-us dotnet standard base-types custom-numeric-format-strings  allowed me to achieve what I wanted   DECLARE  n DECIMAL 9 6   1 23  SELECT  n -- gt  1 230000 SELECT FORMAT  n   0          -- gt  1 23   If you want your number to have at least one trailing zero  so 2 0 does not become 2  use a format string like 0 0       The decimal point is localised  so cultures that use a comma as decimal separator will encounter a comma output where the   is  Of course  this is the discouragable practice of having the data layer doing formatting  but in my case there is no other layer  the user is literally running a stored procedure and putting the result in an email

User · Answer

I had a similar problem  needed to trim trailing zeros from numbers like  xx0000 x00000 xxx000  I used   select LEFT code LEN code  1 - PATINDEX    1-Z    REVERSE code    from Tablename   Code is the name of the field with the number to be trimmed  Hope this helps someone else

User · Answer

A decimal 9 6  stores 6 digits on the right side of the comma   Whether to display trailing zeroes or not is a formatting decision  usually implemented on the client side   But since SSMS formats float without trailing zeros  you can remove trailing zeroes by casting the decimal to a  float   select      cast 123 4567 as DECIMAL 9 6       cast cast 123 4567 as DECIMAL 9 6   as float    prints   123 456700  123 4567    My decimal separator is a comma  yet SSMS formats decimal with a dot   Apparently a known issue

User · Answer

Another option     I don t know how efficient this is but it seems to work and does not go via float   select replace rtrim replace         replace rtrim replace cast  value as varchar 40     0                0                                    The middle line strips off trailing spaces  the outer two remove the point if there are no decimal digits

User · Answer

Try this   select Cast  Cast   ROUND  35 457514   2   100  as Int  as float    100

[sql] Remove trailing zeros from decimal in SQL Server

Examples related to sql

Examples related to tsql

Examples related to sql-server-2008

Examples related to formatting

Examples related to decimal