萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 內建擴展預存程序 xp_sscanf / xp_sprintf:字串解析與格式化新選擇

SQL Server 提供兩個未公開但實用的擴展預存程序:xp_sscanf (解析) 與 xp_sprintf (格式化),能大幅簡化固定格式字串的拆解與組裝,避免大量 SUBSTRING/CHARINDEX 或字串串聯的痛苦。

1. xp_sscanf:解析固定格式字串

類似 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)。

2. xp_sprintf:安全格式化字串

類比 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 進程崩潰

現代替代方案

  • 解析:SQL 2016+ STRING_SPLIT、OPENJSON、CLR 正則表達式。
  • 格式化:FORMAT() (SQL 2012+)、CONCAT()、QUOTENAME() 防注入。
  • 動態 SQL:sp_executesql 參數化,避免字串拼接注入風險。
copyright © 萬盛學電腦網 all rights reserved