T-SQL 計算子字串在父字串出現次數：邏輯缺陷分析與正確實作

前言：網路流傳版本隱含嚴重邏輯錯誤

需求：計算字串 @chr 在字串 @str 中出現幾次。網路上廣為流傳的「循環截斷法」存在**起始位置計算錯誤**，導致重疊/連續匹配時計數錯誤。本文還原錯誤現場、根因分析、提供修正版與集合化替代方案。

錯誤版本還原與測試

CREATE FUNCTION dbo.f_getcharcount_false (
    @str VARCHAR(8000), @chr VARCHAR(8000)
) RETURNS INT
AS BEGIN
    DECLARE @re INT = 0, @i INT;
    SELECT @i = CHARINDEX(@chr, @str) + 1;
    WHILE @i > 1
    BEGIN
        SELECT @re = @re + 1,
               @str = SUBSTRING(@str, @i, 8000),  -- 錯誤在此！
               @i = CHARINDEX(@chr, @str) + 1;
    END
    RETURN @re;
END

測試案例：SELECT dbo.f_getcharcount_false('df ththth', 'tht')

• 預期結果： 1 (僅第 4-6 字元匹配 tht)。
• 實際回傳： 2 (錯誤)。

根因分析：截斷起始位置偏移

找到匹配位置 `CHARINDEX(@chr, @str)` 後，錯誤版本用 `@i = 位置 + 1` 作為 `SUBSTRING` 起始點。

• 原字串：df ththth (索引 1-based)。
• 第一次匹配 tht 位置 = 4。
• 錯誤版本：`@i = 5`，`SUBSTRING(@str, 5, ...)` → 剩餘字串 hth。
• 殘留字串 hth 中 CHARINDEX('tht') 失敗，但若模式為 'th'，則 hth 位置 2 匹配 th，**將原本屬於上一匹配尾部的字元重複使用**，導致重複計數。

修正版本：截斷起點 = 匹配位置 + 模式長度

CREATE FUNCTION dbo.f_getcharcount_true (
    @str VARCHAR(8000), @chr VARCHAR(8000)
) RETURNS INT
AS BEGIN
    DECLARE @re INT = 0, @i INT;
    SELECT @i = CHARINDEX(@chr, @str);
    WHILE @i > 0
    BEGIN
        SELECT @re = @re + 1,
               @str = SUBSTRING(@str, @i + LEN(@chr), 8000),  -- 關鍵修正：跳過整個匹配模式
               @i = CHARINDEX(@chr, @str);
    END
    RETURN @re;
END

• `@i` 直接存 `CHARINDEX` 回傳值 (0 表示未找到)，迴圈條件 `@i > 0` 更直觀。
• `SUBSTRING` 起始點 = `@i + LEN(@chr)`，**完整跳過匹配區段**，避免重疊/連續字元被重複掃描。

驗證修正版

SELECT dbo.f_getcharcount_true('df ththth', 'tht');  -- 1 ✓
SELECT dbo.f_getcharcount_true('aaa', 'aa');        -- 1 (非重疊) / 2 (重疊視業務定義)
-- 注意：本函數為非重疊計數。重疊計數需不同邏輯 (如遞迴 CTE)。

集合化替代方案：無迴圈、高效能、適用大字串 (SQL Server 2016+)

-- 利用 STRING_SPLIT (需相容性等級 130+)
-- 原理：以目標子字串為分隔符分割，陣列長度 - 1 = 出現次數
CREATE FUNCTION dbo.GetSubstringCount_SetBased (
    @str NVARCHAR(MAX), @chr NVARCHAR(4000)
) RETURNS INT
AS BEGIN
    IF @chr = '' RETURN 0;
    DECLARE @cnt INT = (SELECT COUNT(*) FROM STRING_SPLIT(@str, @chr)) - 1;
    RETURN CASE WHEN @cnt < 0 THEN 0 ELSE @cnt END;
END;

優點： 無迴圈、優化器可並行、支援 MAX 類型、程式碼極簡。
缺點： `STRING_SPLIT` 不保證順序 (但計數不影響)；舊版本不可用。

效能對比 (字串長度 10萬、模式長度 3、出現 1萬次)

總結與建議

1. 審慎複製網路代碼： 即使邏輯看似正確，邊界條件 (空字串、重疊、連續、單字元) 極易疏漏。
2. 優先集合化思維： 字串計數本質為分割/掃描，`STRING_SPLIT` / `TRANSLATE` / 遞迴 CTE 遠優於逐字元迴圈。
3. 單元測試覆蓋邊界： 空輸入、模式長於字串、重疊模式 (aaa/aa)、特殊字元 (%, _, [, ])。
4. 大字串場景： 考慮 CLR 正則表達式 (`Regex.Matches`) 或全文檢索 `CONTAINS`/`FREETEXT`。