SQL Server 字串解析與格式化神器：xp_sscanf 與 xp_sprintf 實戰範例

SQL Server 內建兩個擴展預存程序 `xp_sscanf`（解析）與 `xp_sprintf`（格式化），能大幅簡化字串拆解與組裝邏輯，避免大量 `SUBSTRING`/`CHARINDEX`/`+` 運算子。

1. xp_sscanf：依格式字串拆解輸入

適用於固定格式字串（IP、日期、CSV 等）。範例：將 IP `192.168.0.1` 拆為四欄位整數。

IF OBJECT_ID('f_getip') IS NOT NULL DROP FUNCTION 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, '.', ' ');
    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;
GO
SELECT * FROM dbo.f_getip('192.168.0.1');
-- 結果：192, 168, 0, 1

格式佔位符：`%s` 字串、`%d` 整數、`%f` 浮點、`%c` 單字元。

2. xp_sprintf：格式化組裝字串

避免複雜引號巢狀，動態 SQL 組裝更清晰。範例：通用單表查詢預存程序。

IF OBJECT_ID('p_select') IS NOT NULL DROP PROC p_select;
GO
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;
GO
EXEC p_select 'sysobjects', 'id,xtype,crdate', 'name', 'p_select';
-- 結果：id=898102240, xtype=P, crdate=2009-08-18...

注意事項

• 兩者皆為**未文件化**擴展預存程序，微軟不保證未來版本相容性，生產環境謹慎使用。
• `xp_sprintf` 緩衝區上限 8000 字元（VARCHAR(8000)），超長需自行分段。
• SQL Server 2012+ 建議改用 `FORMAT`、`PARSE`、`TRY_PARSE` 等標準函數，或 CLR 實作。