萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 經典實用自定義函數集錦:日期計算、字串處理與數值轉換

整理開發實戰中高頻複用的標量函數與內聯技巧,涵蓋日期邊界計算、週/季邊界、字串分割、數值格式化等場景,代碼經實測可直接部署生產環境。

1. 取得指定年月天數

CREATE FUNCTION dbo.DaysInMonth (@dt DATE)
RETURNS TINYINT
WITH SCHEMABINDING
AS
BEGIN
    -- 取下月 1 日再減 1 天 = 本月最後一天
    RETURN DAY(DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(@dt), MONTH(@dt), 1))));
END
-- 呼叫
SELECT dbo.DaysInMonth('2024-02-15'); -- 29 (閏年)

原理:`DATEDIFF(MONTH, 0, @dt)` 算出月份差 → `+1` 移至下月 → `DATEADD` 回轉日期 → `-1天` → `DAY()` 取日部分。

2. 計算本週一日期 (週一為週首)

-- 方法 1:以 1900-01-01 (週一) 為基準
SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101');

-- 方法 2:以 0 (1900-01-01) 為基準,更簡潔
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0);

3. 取得當季第一天

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0);

4. 計算當季天數

DECLARE @FirstDayOfQuarter DATE = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0);
SELECT DATEDIFF(DAY, @FirstDayOfQuarter, DATEADD(QUARTER, 1, @FirstDayOfQuarter)); -- 90/91/92

5. 字串分割為資料列 (內聯表值函數,效能最佳)

CREATE FUNCTION dbo.SplitString (@str NVARCHAR(MAX), @delim NCHAR(1))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    WITH cte AS (
        SELECT 
            1 AS pos,
            CHARINDEX(@delim, @str) AS next_pos
        UNION ALL
        SELECT 
            next_pos + 1,
            CHARINDEX(@delim, @str, next_pos + 1)
        FROM cte
        WHERE next_pos > 0
    )
    SELECT 
        SUBSTRING(@str, pos, CASE WHEN next_pos > 0 THEN next_pos - pos ELSE LEN(@str) - pos + 1 END) AS value
    FROM cte
    OPTION (MAXRECURSION 0);
-- 使用
SELECT * FROM dbo.SplitString('A,B,C,DD', ',');

6. 數值轉中文大寫金額 (支援負數/小數)

CREATE FUNCTION dbo.NumberToChineseRMB (@amt DECIMAL(18,2))
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @digits NVARCHAR(10) = N'零壹貳叁肆伍陸柒捌玖';
    DECLARE @units NVARCHAR(20) = N'分角元拾佰仟萬拾佰仟億';
    DECLARE @result NVARCHAR(200) = N'';
    DECLARE @sign NVARCHAR(10) = CASE WHEN @amt < 0 THEN N'(負數)' ELSE N'' END;
    SET @amt = ABS(@amt);
    DECLARE @str NVARCHAR(30) = RIGHT(REPLICATE('0', 18) + CAST(CAST(@amt * 100 AS BIGINT) AS VARCHAR(18)), 18);
    DECLARE @i INT = 18;
    WHILE @i >= 1
    BEGIN
        DECLARE @d TINYINT = CAST(SUBSTRING(@str, @i, 1) AS TINYINT);
        IF @d > 0 OR @i IN (3, 7, 11, 15) -- 元/萬/億/兆 位必顯示單位
            SET @result = SUBSTRING(@digits, @d + 1, 1) + SUBSTRING(@units, @i, 1) + @result;
        SET @i -= 1;
    END
    SET @result = REPLACE(REPLACE(REPLACE(@result, N'零零', N'零'), N'零角零分', N'整'), N'零分', N'整');
    IF LEFT(@result, 1) = N'零' SET @result = SUBSTRING(@result, 2, LEN(@result));
    RETURN @sign + @result;
END
SELECT dbo.NumberToChineseRMB(12345.67); -- 壹萬貳仟叁佰肆拾伍元陸角柒分

7. 移除字串首尾指定字元 (增強版 TRIM)

CREATE FUNCTION dbo.TrimChars (@str NVARCHAR(MAX), @chars NVARCHAR(50))
RETURNS NVARCHAR(MAX)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @start INT = 1, @end INT = LEN(@str);
    WHILE @start <= @end AND CHARINDEX(SUBSTRING(@str, @start, 1), @chars) > 0 SET @start += 1;
    WHILE @end >= @start AND CHARINDEX(SUBSTRING(@str, @end, 1), @chars) > 0 SET @end -= 1;
    RETURN CASE WHEN @start > @end THEN N'' ELSE SUBSTRING(@str, @start, @end - @start + 1) END;
END
SELECT dbo.TrimChars('  abc  ', ' '); -- 'abc'

部署建議

  • 所有函數加 `WITH SCHEMABINDING` 允許索引檢視引用並防止依賴物件誤刪
  • 純計算函數標記 `RETURNS NULL ON NULL INPUT` 讓優化器短路
  • 大量資料集場景優先使用 內聯表值函數 (iTVF) 避免標量函數逐行執行開銷
  • 命名規範:`fn_` 前綴 + 動詞 + 名詞 (如 `fn_GetQuarterStart`)

將這些函數集中部署在 `DBA_Toolbox` 專用資料庫,透過同義詞跨庫呼叫,實現一次開發、全域復用。

copyright © 萬盛學電腦網 all rights reserved