SQL STUFF 函數完整指南：字串刪除插入雙重操作實戰

函數定位

STUFF：在字串指定位置刪除指定長度，並插入新字串。
語法：STUFF ( character_expression , start , length , replacement_expression )

參數詳解

經典範例

-- 在 'abcdef' 第 2 位刪 3 字符 (bcd)，插入 'ijklmn'
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
-- 結果：aijklmnef

實戰場景

1. 日期格式轉換（去分隔符 + 插新格式）

DECLARE @dt CHAR(10) = '2024-01-15'
SELECT STUFF(STUFF(@dt, 5, 1, '/'), 8, 1, '/')  -- 2024/01/15

2. 身分證/手機號碼遮罩

DECLARE @id VARCHAR(18) = 'A123456789'
SELECT STUFF(@id, 2, 6, '******')  -- A******789

DECLARE @phone VARCHAR(10) = '0912345678'
SELECT STUFF(@phone, 4, 4, '****')  -- 091****678

3. 動態 SQL 欄位列表構建（去除尾隨逗號）

DECLARE @cols VARCHAR(MAX) = 'col1,col2,col3,'
SELECT STUFF(@cols, LEN(@cols), 1, '')  -- col1,col2,col3

4. XML 路徑字串拼接輔助

SELECT STUFF((
    SELECT ',' + name FROM sys.tables FOR XML PATH('')
), 1, 1, '')  -- 去除首逗號：table1,table2,table3

返回類型規則

• 輸入為字串類型 → 返回字串（varchar/nvarchar）。
• 輸入為二進制類型 → 返回二進制（varbinary）。
• 結果長度超過類型上限 → 報錯。

STUFF vs REPLACE vs SUBSTRING

注意事項

• start/length 為負 → 返回空字串（非 NULL）。
• start > 字串長度 → 返回空字串。
• length > 剩餘長度 → 刪除至結尾。
• 二進制資料同樣適用，位元組級操作。

總結

STUFF 是字串「微手術」工具：精確定位、刪除、插入一氣呵成。掌握它能優雅解決遮罩、格式化、動態 SQL 拼接等高頻需求。