整理開發實戰中高頻複用的標量函數與內聯技巧,涵蓋日期邊界計算、週/季邊界、字串分割、數值格式化等場景,代碼經實測可直接部署生產環境。
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()` 取日部分。
-- 方法 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);
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0);
DECLARE @FirstDayOfQuarter DATE = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0);
SELECT DATEDIFF(DAY, @FirstDayOfQuarter, DATEADD(QUARTER, 1, @FirstDayOfQuarter)); -- 90/91/92
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', ',');
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); -- 壹萬貳仟叁佰肆拾伍元陸角柒分
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'
將這些函數集中部署在 `DBA_Toolbox` 專用資料庫,透過同義詞跨庫呼叫,實現一次開發、全域復用。