您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2008 使用 CTE 將字串分割轉換為資料表

編輯：關於SqlServer

需求場景

應用層傳入逗點分隔的 ID 字串（如 '1,2,4,8'），存儲過程內需轉換為資料表值參數進行 IN 查詢或 JOIN 操作。SQL Server 2008 引入的 遞迴 CTE（Common Table Expression） 完美解決此問題，無需迴圈或 CLR。

核心函數實作：strToTable

CREATE FUNCTION [dbo].[strToTable] (
    @array VARCHAR(MAX),
    @del CHAR(1)
)
RETURNS @listTable TABLE (item INT)
AS
BEGIN
    WITH rep (item, list) AS (
        -- 錨點：取第一個分隔符前的子字串
        SELECT 
            SUBSTRING(@array, 1, CHARINDEX(@del, @array, 1) - 1) AS item,
            SUBSTRING(@array, CHARINDEX(@del, @array, 1) + 1, LEN(@array)) + @del AS list
        UNION ALL
        -- 遞迴：逐個取出後續項目
        SELECT 
            SUBSTRING(list, 1, CHARINDEX(@del, list, 1) - 1) AS item,
            SUBSTRING(list, CHARINDEX(@del, list, 1) + 1, LEN(list)) AS list
        FROM rep
        WHERE LEN(rep.list) > 0
    )
    INSERT INTO @listTable
    SELECT item FROM rep
    OPTION (MAXRECURSION 0);  -- 允許超過 100 級遞迴
    RETURN;
END
GO

呼叫範例

DECLARE @array VARCHAR(MAX) = '1,2,4,8';
SELECT item FROM dbo.strToTable(@array, ',');
-- 結果：
-- item
-- ----
-- 1
-- 2
-- 4
-- 8

關鍵技術解析

技術點	說明
遞迴 CTE	`WITH ... AS (anchor UNION ALL recursive)` 結構，錨點執行一次，遞迴部份重複執行直到終止條件
`CHARINDEX` 定位分隔符位置	配合 `SUBSTRING` 切割字串
尾部加分隔符	`+ @del` 確保最後一個項目也能被 `CHARINDEX` 找到
`OPTION (MAXRECURSION 0)`	移除預設 100 級遞迴限制，支援超長字串分割
回傳表值函數	`RETURNS @table TABLE` 直接可在 `FROM` 子句使用

進階變體：支援字串型別項目

CREATE FUNCTION [dbo].[strToTableStr] (
    @array NVARCHAR(MAX),
    @del NCHAR(1)
)
RETURNS @listTable TABLE (item NVARCHAR(4000))
AS
BEGIN
    WITH rep (item, list) AS (
        SELECT 
            SUBSTRING(@array, 1, CHARINDEX(@del, @array, 1) - 1),
            SUBSTRING(@array, CHARINDEX(@del, @array, 1) + 1, LEN(@array)) + @del
        UNION ALL
        SELECT 
            SUBSTRING(list, 1, CHARINDEX(@del, list, 1) - 1),
            SUBSTRING(list, CHARINDEX(@del, list, 1) + 1, LEN(list))
        FROM rep
        WHERE LEN(rep.list) > 0
    )
    INSERT INTO @listTable SELECT item FROM rep OPTION (MAXRECURSION 0);
    RETURN;
END
GO

-- 使用範例
SELECT item FROM dbo.strToTableStr(N'苹果,香蕉,橘子', N',');

效能與替代方案比較

方案	適用版本	效能	維護性	備註
遞迴 CTE 函數	SQL 2008+	中等（萬筆內佳）	高	純 T-SQL，無外部依賴
`STRING_SPLIT`	SQL 2016+	高（原生優化）	最高	不保證順序，無索引值
`OPENJSON`	SQL 2016+	高	高	需 JSON 格式 `['1','2']`
XML 解析	SQL 2005+	低	低	舊版相容方案
CLR 函數	SQL 2005+	最高	中	需啟用 CLR、部署組件