CODEWITHSUNDEEP
sqlt-sql
lije
lije

Reputation: 420

Removing trailing zeros in case statement

I've been trying to remove the trailing zeros from a column of a table. It works well when I try to remove the zeros from the column. However, when I use it with a case statement (to remove the zeros when a flag is turned on, and to keep them when a flag is turned off) it doesn't work properly. It doesn't recognize the flag. For example, I've hard coded the column as a constant value; while 1=0 (false), it is retrieving the value removing the zeros. It should be true in the else statement. 

SELECT CASE WHEN 1=0 THEN cast(CAST(123.45000 AS decimal(6,2)) as float)  
ELSE   
'123.456700'   
END


SELECT CASE WHEN 1=0 THEN CONVERT(DOUBLE PRECISION, 123.456700) 
ELSE   
'123.456700'   
END

Why is this happening? Can anyone help me with this?

The above is well explained by @Tim below.

However, it doesn't remove the zeros at all in a table. It doesn't recognize the flag at all. Here is an example: 

CREATE TABLE #tablea 
(item CHAR(2), name VARCHAR(10), amount DECIMAL(9,2)) 

INSERT INTO #tablea 
VALUES ('AB', 'D1', 1.10), 
       ('AB', 'D2', 1.00), 
       ('AB', 'D3', 0.90), 
       ('AB', 'D4', 0.09)    

DECLARE @flag INT = 1

SELECT CASE WHEN @flag = 1 
THEN CAST(CAST(amount AS DECIMAL(6,2)) AS VARCHAR(max))
ELSE amount END 
FROM #tablea

Upvotes: 1

Views: 594

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82484

You can't remove trailing zeroes from a decimal data type. The decimal/numeric data types are fixed point data type:

Numeric data types that have fixed precision and scale. Decimal and numeric are synonyms and can be used interchangeably.

This means that the decimal point is in a fixed position within the stored number, unlike float and read which are floating point data types - so for a decimal(9,2) there will always be two digits to the right of the decimal point - and for numeric(5,3) where will always be three digits to the left of the decimal point.

If precision is not very important, you can convert to float - but you should be aware that unlike decimal, float is an approximate data type.
Please note that you would still have to convert both branches of the case statement to a varhcar otherwise SQL Server will implicitly convert both branches to float and it will look like the flag is being ignored.

DECLARE @flag INT = 1

SELECT 
        CASE WHEN @flag = 1 
            THEN CAST(CAST(amount AS float) as varchar(30))
            ELSE CAST(amount as varchar(30)) 
        END As [Remove trailing zeros],

        -- This is to show the opposite branch
        CASE WHEN @flag = 0 
            THEN CAST(CAST(amount AS float) as varchar(30))
            ELSE CAST(amount as varchar(30)) 
        END As [Include trailing zeros]
FROM #tablea

Results:

Remove trailing zeros   Include trailing zeros
1.1                     1.10
1                       1.00
0.9                     0.90
0.09                    0.09

Upvotes: 1

lije
lije

Reputation: 420

@Tim, thanks. It works in the demo you showed. But it doesn't remove the zeros at all in a table. It doesn't recognize the flag at all. Here is an example: 

CREATE TABLE #tablea 
(item CHAR(2), name VARCHAR(10), amount DECIMAL(9,2)) 

INSERT INTO #tablea 
VALUES ('AB', 'D1', 1.10), 
       ('AB', 'D2', 1.00), 
       ('AB', 'D3', 0.90), 
       ('AB', 'D4', 0.09)    

DECLARE @flag INT = 1

SELECT CASE WHEN @flag = 1 
THEN CAST(CAST(amount AS DECIMAL(6,2)) AS VARCHAR(max))
ELSE amount END 
FROM #tablea

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521674

As @JNevill commented, what is happening here is that an implicit conversion is happening in the ELSE branch of your CASE expression, converting the string literal into a float, thereby removing the trailing zeroes when it gets printed. One option would be to cast the IF portion to VARCHAR:

SELECT
    CASE WHEN 1=0
         THEN CAST(CAST(123.45000 AS decimal(6,2)) AS varchar(max))  -- a string
         ELSE '123.456700' END  -- also a string

Demo

Note that in certain versions of SQL (other than yours) your CASE expression would not even run without error. It just so happens that a silent conversion is taking place here.

Upvotes: 3

Related Questions