A client came to me with another string formatting problem. I would never recommend formatting strings in T-SQL, but rather to do formatting on the client side. The question was to strip trailing zeros from a numeric value stored in a string but retain the decimals when they are not zero.
|1.0501||needs to show||1.0501|
|1.0500||needs to show||1.05|
|1.0000||needs to show||1|
This is easy to do by replacing all zeroes with a space and then using RTRIM() to remove the spaces on the right. Finally replace the spaces again with zeroes and you have the correct result. Except when all decimals were zero, in that case the decimal point needs to be suppressed as well. I poured the code in a function that is easier to use.
Note: The function contains some addition logic to determine if the decimal separator is a point or a comma.