SQL Server 進階字串處理：xp_sscanf 與 xp_sprintf 實戰範例

擴展預存程序簡介

• xp_sscanf：依格式字串解析輸入，將片段輸出至變數（類 C sscanf）。
• xp_sprintf：依格式字串組合多個變數為單一字串（類 C sprintf），避免大量 + 串接與引號巢狀。

兩者皆為未文件化擴展程序，生產環境請評估相容性風險。

範例一：解析 IP 位址

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, '.', ' ');
    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 @s1, @s2, @s3, @s4;
    RETURN;
END;

SELECT * FROM dbo.f_getip('192.168.0.1');
-- 結果：192, 168, 0, 1

範例二：動態組合 SQL 陳述式

CREATE PROC 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;

EXEC p_select 'sysobjects', 'id, xtype, crdate', 'name', 'p_select';

優缺點評估

現代替代方案

• SQL Server 2017+：STRING_AGG、CONCAT_WS、FORMAT。
• CLR 使用者定義函數：功能更強、型別安全。
• 參數化動態 SQL + sp_executesql：最安全、計畫可重用。

了解 xp_sscanf/xp_sprintf 有助維護舊系統，新專案建議採用原生函數或 CLR。