SQL中統計長字串內特定子字串出現次數的高效技巧

在SQL開發中，常需統計某字串在長文本中出現的次數（如日誌解析、資料清洗）。若逐字符分析極其低效，實際上可巧用REPLACE與LEN/LENGTH函數組合，以集合運算思維秒級解決。

核心原理

公式：LENGTH(REPLACE(原字串, 目標子字串, 目標子字串+目標子字串)) - LENGTH(原字串)

邏輯：將目標子字串替換為「兩倍長度」的字串，總長度增量除以目標子字串長度即為出現次數。若目標長度為1（如單字符'-'），增量直接等於次數。

實戰範例

場景：統計 'X-BGS-2010-09-15-001' 中 '-' 出現次數

DECLARE @a VARCHAR(100);
SET @a = 'X-BGS-2010-09-15-001';
SELECT LEN(REPLACE(@a, '-', '--')) - LEN(@a);  -- 結果：5

通用表查詢寫法

-- 統計表a中欄位b包含字符'c'的次數
SELECT LEN(REPLACE(b, 'c', 'cc')) - LEN(b) FROM a;

注意：LEN函數名在不同資料庫可能異名（MySQL用LENGTH、CHAR_LENGTH；SQL Server用LEN；Oracle用LENGTH），請依實際環境調整。

進階變體

• 多字符子串統計：同樣適用，如統計 'abc' 出現次數，替換為 'abcabc'，增量除以3。
• 不區分大小寫：搭配LOWER/UPPER統一大小寫後再計算。
• 排除重疊匹配：此法屬非重疊計數，符合大多數業務需求。

效能優勢

• 單條SQL完成，無需游標/迴圈/自定義函數
• 利用資料庫內建字串函數，執行計畫穩定
• 適用於百萬級資料批次統計

這個技巧體現了SQL集合運算思維：用「整體替換+長度差」替代「逐字符迭代」，是字串處理中的經典智慧。