SQL Server 常用字串函數完整參考手冊

T-SQL 字串操作函數分類與實戰範例

SQL Server 提供豐富的字串函數支援資料清洗、格式化、解析、搜尋等需求。本文依功能分類整理，附上效能提示與版本相容性說明。

一、字元代碼轉換

二、大小寫轉換

-- 基本轉換
UPPER('Hello World')   -- 'HELLO WORLD'
LOWER('Hello World')   -- 'hello world'

-- 標題大小寫 (首字大寫) - 需自行實作或使用 CLR
-- 簡易版：
SELECT UPPER(LEFT(@str,1)) + LOWER(SUBSTRING(@str,2,LEN(@str)));

三、去除空白字元

四、子字串擷取

五、字串搜尋與定位

六、字串建構與修飾

七、格式化與轉換

八、進階解析函數 (2016+)

-- JSON 路徑提取
JSON_VALUE(@json, '$.store.book[0].author')  -- 純量值
JSON_QUERY(@json, '$.store.book')            -- 物件/陣列片段
JSON_MODIFY(@json, '$.price', 19.99)         -- 修改回傳新 JSON

-- 字串分割 (替代自訂函數)
SELECT value, ordinal FROM STRING_SPLIT('A,B,C', ',') ORDER BY ordinal;
-- ordinal 欄位需搭配 ROW_NUMBER() 或 OPENJSON 取得順序

實戰範例：常見字串處理場景

1. 移除 HTML 標籤

CREATE FUNCTION dbo.fn_StripHtml(@Html NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS BEGIN
    WHILE CHARINDEX('<', @Html) > 0
    BEGIN
        DECLARE @Start INT = CHARINDEX('<', @Html);
        DECLARE @End INT = CHARINDEX('>', @Html, @Start);
        IF @End = 0 BREAK;
        SET @Html = STUFF(@Html, @Start, @End - @Start + 1, '');
    END
    RETURN @Html;
END;

2. 解析 CSV 行 (處理引號、逗號)

-- 簡易版：假設無巢狀引號
SELECT * FROM STRING_SPLIT(REPLACE(@csv, '","', '|'), '|');

-- 完整版：使用 OPENJSON (2016+)
DECLARE @Json NVARCHAR(MAX) = '["' + REPLACE(REPLACE(@csv, '"', '""'), ',', '","') + '"]';
SELECT value FROM OPENJSON(@Json);

3. 產生固定長度代碼 (補零)

-- RIGHT 技巧
SELECT RIGHT('000000' + CAST(@Num AS VARCHAR(6)), 6);  -- 6 位數補零

-- FORMAT (2012+)
SELECT FORMAT(@Num, 'D6');  -- 相同效果

4. 字串相似度比較 (簡易版)

-- Levenshtein 距離需 CLR 或自訂函數
-- 簡易 Jaccard 相似度 (字元集合交集/聯集)
-- 實際專案建議使用全文搜尋或 Azure Cognitive Services

效能優化關鍵提醒

• ✅ 避免在 WHERE/JOIN 中對欄位使用函數：WHERE UPPER(Name) = 'JOHN' → 無法使用索引。改為 WHERE Name = 'John' COLLATE Latin1_General_CI_AI
• ✅ 大量字串串接使用 XML PATH 或 STRING_AGG (2017+)：迴圈 CONCAT 效能差
• ✅ CHARINDEX 優於 PATINDEX：無萬用字元需求時
• ✅ SUBSTRING/STUFF 比 LEFT/RIGHT 靈活：但 LEFT/RIGHT 可讀性佳
• ✅ NCHAR/NVARCHAR 處理 Unicode：避免隱含轉換導致索引失效
• ✅ FORMAT 效能較差：高頻呼叫改用 CONVERT + 風格代碼

版本相容性速查表