[MSSQL] Remove trailing zeros from decimal

以下提供三種輸出時，可將Decimal小數點後的0移除方式

Format(SQL SEVER 2012+)

SELECT FORMAT(CAST(1234.56789 AS DECIMAL(16, 5)), '0.#####')   -- 1234.56789
SELECT FORMAT(CAST(1234.56700 AS DECIMAL(16, 5)), '0.#####')   -- 1234.567

Decimal To Float

SELECT CAST(CAST(1234.56789 AS DECIMAL(16, 5)) AS FLOAT)  -- 1234.56789
SELECT CAST(CAST(1234.56700 AS DECIMAL(16, 5)) AS FLOAT)  -- 1234.567

撰寫Function將Decimal轉換為字串

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FN_DecimalToString]') AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[FN_DecimalToString]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[FN_DecimalToString] ( @inputVal sql_variant )
RETURNS
    VARCHAR(50)
AS
BEGIN

    DECLARE @s AS VARCHAR(50), 
            @r AS VARCHAR(50), 
            @fs AS VARCHAR(50)

    SELECT
        -- 將 inputVal 轉為VARCHAR
        @s = CAST(@inputVal AS VARCHAR(50)),
        
        -- 反轉字串
        @r = REVERSE(@s),
        
        -- 去除開頭為0的字串(直到非0)，之後再把字串反轉回來
        --   PATINDEX為找到符合pattern的第一個起始位置
        --   SUBSTRING(expression, start, length)
        @fs = REVERSE(SUBSTRING(@r, PATINDEX('%[^0 ]%', @r), LEN(@r))),

        -- 判斷如結尾為'.'，則將'.'移除 ; 反之則直接回傳fs
        --   以下是防止 inputVal為 1234.00 而經過上方處理則會取得'1234.'
        @fs =
            CASE
                WHEN RIGHT(@fs, 1) = '.' THEN LEFT(@fs, LEN(@fs) - 1)
                ELSE @fs
            END

    RETURN @fs
END

GO

參考資料

1. FORMAT (Transact-SQL)
2. PATINDEX (Transact-SQL)
3. SUBSTRING (Transact-SQL)

如有錯誤或建議，歡迎留言指教，謝謝!!
(相關內容如有侵犯隱私或著作權，請協助通知刪除，感謝)