SQL Server 提供兩個未公開但實用的擴展預存程序:xp_sscanf (解析) 與 xp_sprintf (格式化),能大幅簡化固定格式字串的拆解與組裝,避免大量 SUBSTRING/CHARINDEX 或字串串聯的痛苦。
類似 C 的 sscanf,依格式字串將來源字串拆解到多個輸出變數。
-- 範例:解析 IP 位址 '192.168.0.1' → 四個整數欄位
IF OBJECT_ID('dbo.f_getip') IS NOT NULL DROP FUNCTION dbo.f_getip;
GO
CREATE FUNCTION dbo.f_getip(@ip VARCHAR(100))
RETURNS @t TABLE (a INT, b INT, c INT, d INT)
AS BEGIN
SET @ip = REPLACE(@ip, '.', ' '); -- 將分隔符統一為空白,配合 %s
DECLARE @s1 VARCHAR(3), @s2 VARCHAR(3), @s3 VARCHAR(3), @s4 VARCHAR(3);
EXEC xp_sscanf @ip, '%s %s %s %s', @s1 OUTPUT, @s2 OUTPUT, @s3 OUTPUT, @s4 OUTPUT;
INSERT INTO @t SELECT CAST(@s1 AS INT), CAST(@s2 AS INT), CAST(@s3 AS INT), CAST(@s4 AS INT);
RETURN;
END;
GO
SELECT * FROM dbo.f_getip('192.168.0.1');
-- 結果:192, 168, 0, 1
格式佔位符:%s 字串、%d 整數、%f 浮點數,支援寬度與精度 (如 %10.2f)。
類比 sprintf,將參數依格式字串組裝成單一字串,自動處理引號逸出、型別轉換,極適合動態 SQL 組裝。
-- 範例:動態查詢預存程序
IF OBJECT_ID('dbo.p_select') IS NOT NULL DROP PROC dbo.p_select;
GO
CREATE PROC dbo.p_select
@tb VARCHAR(100), @cols VARCHAR(100), @wherecol VARCHAR(100), @value VARCHAR(100)
AS BEGIN
DECLARE @s VARCHAR(8000);
EXEC xp_sprintf @s OUTPUT, 'SELECT %s FROM %s WHERE %s = ''%s''', @cols, @tb, @wherecol, @value;
EXEC(@s);
END;
GO
EXEC dbo.p_select 'sysobjects', 'id, xtype, crdate', 'name', 'p_select';
-- 產生並執行:SELECT id, xtype, crdate FROM sysobjects WHERE name = 'p_select'
| 優點 | 缺點/風險 |
|---|---|
| 程式碼極度精簡、可讀性高 | 非官方文件化功能,未來版本可能移除 |
| 避免手動串聯字串的引號錯誤 | xp_sprintf 緩衝區 8000 字元上限,超長動態 SQL 需分段 |
xp_sscanf 解析固定格式 (日誌、匯入檔) 極快 | 無法處理複雜/不固定格式,需搭配 PATINDEX/REGEX (CLR) |
| 無需 CLR 啟用即可使用 | 擴展預存程序在記憶體中執行,嚴重錯誤可能導致 SQL Server 進程崩潰 |
STRING_SPLIT、OPENJSON、CLR 正則表達式。FORMAT() (SQL 2012+)、CONCAT()、QUOTENAME() 防注入。sp_executesql 參數化,避免字串拼接注入風險。