CODEWITHSUNDEEP
sql-servert-sqlcase
lije
lije

Reputation: 420

Removing Leading and Trailing Zeros along with a Case

I wanted to remove both leading and trailing zeros of a decimal record (for example: having .08 of a record 0.0800). This works very fine. 

SELECT REPLACE(RTRIM(LTRIM(REPLACE(cast(CAST(0.0800 AS decimal(6,2)) as float),'0',' '))),' ','0')

However, when I try to use it with a case, it is removing only the trailing zeros.

DECLARE @char int = 1

SELECT  
CASE WHEN @char = 1 THEN REPLACE(RTRIM(LTRIM(REPLACE(cast(CAST(0.0800 AS decimal(6,2)) as float),'0',' '))),' ','0') 
ELSE 0.01
END

gives 0.08, while I'm expecting to have .08

However, this one gives 0.08

DECLARE @char int = 1

SELECT  
CASE WHEN @char = 1 THEN REPLACE(RTRIM(LTRIM(REPLACE(cast(CAST(0.0800 AS decimal(6,2)) as float),'0',' '))),' ','0') 
ELSE cast(cast(0.01 as decimal(6,2)) as varchar) 
END

can anyone explain the reason? I'm just wondering.

Upvotes: 1

Views: 59

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

This really belongs in the presentation layer. That stated, there is an alternative where you can format the number as string.

To be clear, format() has some great functionality, but the performance can suffer.

Example

Select format(0.080,'#.######')

Returns

.08

Upvotes: 1

forpas
forpas

Reputation: 164099

From CASE (Transact-SQL):

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

And as you can find in Data type precedence (Transact-SQL) any number data type has higher precedence than any varchar type, so this:

CASE WHEN @char = 1 THEN REPLACE(RTRIM(LTRIM(REPLACE(cast(CAST(0.0800 AS decimal(6,2)) as float),'0',' '))),' ','0') 
ELSE 0.01
END

will return the same data type as 0.01.
This means that although the REPLACE() function returns .08 it will be implicitly converted to 0.08 and this is what you see.
In the 2nd version of your query where all branches of the CASE statement return the same data type, since the ELSE part returns varchar, the returned type of the statement is varchar, so you see what you expect: .08

Upvotes: 3

Related Questions