SQL Server retains decimals (cont. a)

After writing the last article, there are friends to give out other ideas, I in the summary in this blog.

Idea one: Convert the output to a string (this is also the idea given in the previous article)

1 1 SELECT   Case   when @a % @b = 0   Then CAST(@a / @b  as VARCHAR( -)) 2 2               ELSE  CAST(CAST(@a * 1.0 / @b  as DECIMAL(Ten,4)) as VARCHAR( +))END '12/5',3 3          Case   when @c % @d = 0   Then CAST(@c / @d  as VARCHAR( -))4 4               ELSE  CAST(CAST(@c * 1.0 / @d  as DECIMAL(Ten,4)) as VARCHAR( +))END '12/3'

View Code

Idea two: Use the Replace () function to determine the form after the decimal point

1 DECLARE @a INT,@b INT,@c INT,@d INT2 SET @a    =  A3 SET @b  = 54 SET @c =  A5 SET @d = 3        6 SELECT REPLACE(CAST(@a * 1.0 / @b  as DECIMAL(Ten,4)),'. 0000',"')'12/5',7        REPLACE(CAST(@c * 1.0 / @d  as DECIMAL(Ten,4)),'. 0000',"')'12/3'

View Code

Idea three: Use the round () function to determine whether the result after rounding is equal to the original result

1 DECLARE @a INT,@b INT,@c INT,@d INT2 SET @a    =  A3 SET @b  = 54 SET @c =  A5 SET @d = 3    6 SELECT       Case    when ROUND(@a * 1.0 / @b,0)= @a * 1.0 / @b   Then  CAST(@a / @b  as VARCHAR( -))7                  ELSE CAST(CAST(@a * 1.0 / @b  as DECIMAL(Ten,4)) as VARCHAR( +))END '12/5',8                  9            Case    when ROUND(@c * 1.0 / @d,0)= @c * 1.0 / @d  Then  CAST(@c / @d  as VARCHAR( -)) Ten                  ELSE CAST(CAST(@c * 1.0 / @d  as DECIMAL(Ten,4)) as VARCHAR( +))END '12/3'          

View Code

The operation results of the above three ideas are as follows: